Re: Update SQL problem

pyates_at_melb.cpr.itg.telecom.com.au (Peter Yates) wrote:

>Hi
 

>I'm doing an update to a table using a sub-query and get
>ORA-01427 single-row subquery returns more than one row.
>How do I get around this?
 

>update table1
>set day=sysdate
>where table1.id = (select id from table2 where field2 like
>'BLAH');
 
>The select returns more than one row so the update fails.
> Thankx in advance

Try:

update table1 a
set day=sysdate
where exists (
select null from table2
where id = a.id and field2 like 'BLAH');

It's much more efficient than using the IN operator because once the condition is met in the EXISTS, the table2 scan can be stopped. It also would help if the fields in the where clause where indexed.

Russ Received on Wed Jan 24 1996 - 00:00:00 CET