Re: SQL. Correlated UPDATE problem.

In article <h3vfasgbv5nq4c1c7bbc82ki4sfbc5degu_at_4ax.com>, on Mon, 14 Feb 2000 at 08:09:58, Thomas Kyte <URL: mailto:tkyte_at_us.oracle.com> writes

>>>UPDATE
>>> customers
>>>SET
>>> cusname = (SELECT Cname FROM Customers_newinfo
>>> WHERE Customers_newinfo.Cno = customers.Cusno)
>>> and exist
>>> (SELECT * FROM Customers_newinfo
>>> WHERE Customers_newinfo.Cno = customers.Cusno)
>>>;

>If customers_newinfo.cno has a unique index ([as it] should for both
>performance and correctness, cno must be unique for the above
>to work else the single row subquery would return >1 row) then it can
>be written as:

>update
> ( select a.cname NEW_CNAME, b.cusname OLD_CNAME
> from customers_newinfo a, customers b
> where a.cno = b.cusno )
> set OLD_CNAME = NEW_CNAME;

Thanks for your prompt and helpful comments which lead me to think more widely about alternative approaches to that problem. However, I am still wondering whether there is an efficient way of expressing

UPDATE xxx
SET yyy = (select ...<a complex select statement> ...) WHERE <the result of the selection is not null>

without repeating the whole select statement in the WHERE clause, as a WHERE EXISTS, and having it executed twice.

Any ideas?

Leonard Will
--

Willpower Information        (Partners: Dr Leonard D Will, Sheena E Will)
Information Management Consultants               Tel: +44 (0)20 8372 0092
27 Calshot Way, Enfield, Middlesex EN2 7BQ, UK.  Fax: +44 (0)20 8372 0094
L.Will_at_Willpower.demon.co.uk            Sheena.Will_at_Willpower.demon.co.uk
---------------- <URL:http://www.willpower.demon.co.uk/> ----------------