Re: SQL. Correlated UPDATE problem.

In article <87rats$92h$1_at_serv1.iunet.it>, on Wed, 9 Feb 2000 at 10:10:07, Marcelllo Cecci <URL: mailto:mcecci_at_seldat.it> writes
>try this
>statement:
>
>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)
>;

Some follow-up questions about this:

1. Does Oracle optimise this sort of question where you need to update a field by the results of a complex SELECT statement only if that result is not null? I.e. if you have to repeat the select statement twice, as above, is it executed twice?
2. If the statement has to be repeated, is it better to select * in the second occurrence, as above, or to select Cname to make the statements identical, or to select a dummy text value such as 'x' to save the system from having to retrieve any data?
3. Is there a neat and easy way to put the results of the statement into a variable (host variable or PL/SQL) that can be used for the "exists" test? Is it worthwhile to do so in terms of speed of execution?

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/> ----------------