Re: Update statement

I thought of option 1 but option 2 is better (well, maybe. Depends). If there is more then one row in sqlloadtable with the same key as a row in basetable, then option 1 will fail. Option 2 will work but you don't know which value will the one that updates b.field1.

"Brian E Dick" <bdick_at_cox.net> wrote in news:qauD9.4313$wc2.267860_at_news2.east.cox.net:

> Two options that I know of:
>
> 1) Put your sqlloadtable in a SET clause subselect and correlate it
> with basetable. Watch out for the subselect, it may return null. If
> that's a problem add a WHERE EXISTS.
>
> update basetable b
> set b.field1 =
> (select t.field1 from sqlloadtable t where b.key = t.key);
>
> or
>
> update basetable b
> set b.field1 =
> (select t.field1 from sqlloadtable t where b.key = t.key)
> where exists
> (select * from sqlloadtable t where b.key = t.key);
>
> 2) Update a derived table that is the result of a join of sqlloadtable
> and basetable.
>
> update
> (select b.key, b.field1 b_field, t.field1 t_field
> from sqlloadtable t, basetable b
> where b.key = t.key)
> set b_field = t_field;
>
> "twoboats" <member_at_dbforums.com> wrote in message
> news:2077078.1037979321_at_dbforums.com...

>>
>> I've come from MSSQL to Oracle. Can any Oracle guru tell me if there's
>> a mechanism in PLSQL that equates to the transact sql version of
>> update that allows the update statement to use more than one table
>> i.e. in TSQL you can do
>>
>> update b
>> set b.field1 = t.field1
>> from sqlloadtable t, basetable b
>> where b.key = t.key
>>
>> Basically, I have a table that I'm updating from data sent in a file
>> that's loaded into a worktable using sqlload.
>>
>> Thanks in advance.
>>
>> Would appreciate email copies of replies to wayne.smith_at_rbs.co.uk
>>
>> --
>> Posted via http://dbforums.com

-- 
Ken Denny
http://www.kendenny.com/