Re: PLSQL/ UPDATE statement

Kevin Burton wrote:
>
>
> In Sybase I have the ability to update columns in a table A by joining
> to another table B. The syntax is'
>
> UPDATE Table 1
> set table1.col1 = Table2.col1,
> table1.col2 = Table2.col3
> WHERE table1.col3 = Table2.col2;
>
> I can't do that in Oracle. Can I do this without creating a cursor? I
want
> to be able to this in a stored procedure.
>
> - Kevin Burton
> pdsinc_at_csi.com
>
>

I believe this should work altho I think its inefficient

Update Tab1
  set col1 = (select tab2.col1 from tab2 where tab1.col3 = tab2.col3),   set col2 = (select tab2.col2 from tab2 where tab1.col3 = tab2.col3) where exists
(select null from tab1, tab2
  where tab1.col3 = tab2.col3)

You could also write a PL/SQL function to emulate the Sybase behavior and then have a SQL statement like

Update Tab1

  Set col1 = func(col,key_value)
  set col2 = func(col,key_value)
where col3 = func(col,key_value)

In this case func is a PL/SQL function, col = column_name, and key_value is the column or value you wish to join on.

The function would then return the appropriate value. You may need some dynamic SQL for this.

HTH RC

--
Posted via CNET Help.com
http://www.help.com/ Received on Wed Jan 12 2000 - 12:30:10 CST