Re: 2 Dumb newbee questions

Galen Boyer wrote:

>>>
>>> update
>>> (
>>> select
>>> ck.col_1 old_col_1,
>>> ck.col_2 old_col_2,
>>> col_3 new_col_1,
>>> col_5 new_col_2
>>> from foo i, bar ck
>>> where i.col_1 = ck.col_1
>>> and i.col_2 = ck.col_2
>>> )
>>> set old_col_1 = new_col_1,
>>> old_col_2 = new_col_2
>>> ;
>>>
>>> Does this help?

>> 
>> Yes this helps, I think I get the point though I know this
>> syntax only from create table statements where an existing
>> table is used to specify the columns.  What I still don't
>> understand, does it make any sense to use this select statement
>> when it's not used in the set clause?  Ok, it limits the rows
>> to be updated to where i.col_1 = ck.col_1 and i.col_2 =
>> ck.col_2 it also provides the reference to the other table
>> being used,
> 
> This is really your answer.
> 
>> but why select any columns ck.col_1 old_col_1, ck.col_2
>> old_col_2, col_3 new_col_1, col_5 new_col_2
> 
> Let me turn it around on you.  How would you describe what the
> update should look like?

Hm, probably something like this:

update foo

    set ( col_1, col_2 ) = ( select i.col_3, ck.col_5

                                 from foo i, bar ck
                                     where i.col_1 = ck.col_1
                                     and   i.col_2 = ck.col_2
                           );

Has my version any disatvantage?

Thanx!

Servus
matthias Received on Tue Jul 20 2004 - 02:45:29 CDT