Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Question
A copy of this was sent to "W. Scott Moore" <sirws_at_hotmail.com>
(if that email address didn't require changing)
On Wed, 1 Dec 1999 10:38:16 -0700, you wrote:
>I am trying to run an update:
>
>UPDATE SDE.CH_ADDRESS A, SDE.ADDRESS_PNT_at_MSGISPROD B
>SET A.LOT_NUM = B.LOT_NUM,
> A.PARCEL = B.PARCEL
>WHERE A.LOT_NUM = 0 AND A.FULL_ADDRESS = B.FULL_ADDRESS
>
>but it doesn't seem to like the use of multiple update tables. I *believe*
>that I used this syntax in MS SQL 6.5... I think I must be a little rusty.
>
>Any help would be greatly appreciated.
>
>Sincerely,
>W. Scott Moore
>City of Chandler, AZ
>
update
( select a.lot_num a_lot_num, b.lot_num b_lot_num,
a.parcel a_parcel, b.parcel b_parcel from SDE.CH_ADDRESS A, SDE.ADDRESS_PNT_at_MSGISPROD B WHERE A.LOT_NUM = 0 AND A.FULL_ADDRESS = B.FULL_ADDRESS ) set a_lot_num = b_lot_num, a_parcel = b_parcel /
or
update SDE.CH_ADDRESS
set ( lot_num, parcel ) = ( select lot_num, parcel
from SDE.ADDRESS_PNT_at_MSGISPROD B where SDE.CH_ADDRESS.LOT_NUM = 0 AND SDE.CH_ADDRESS.FULL_ADDRESS = B.FULL_ADDRESS) where exists ( select lot_num, parcel from SDE.ADDRESS_PNT_at_MSGISPROD B where SDE.CH_ADDRESS.LOT_NUM = 0 AND SDE.CH_ADDRESS.FULL_ADDRESS = B.FULL_ADDRESS)/
or
update SDE.CH_ADDRESS
set ( lot_num, parcel ) = ( select lot_num, parcel
from SDE.ADDRESS_PNT_at_MSGISPROD B where SDE.CH_ADDRESS.LOT_NUM = 0 AND SDE.CH_ADDRESS.FULL_ADDRESS = B.FULL_ADDRESS)where ( lot_num, parcel ) in ( select lot_num, parcel from SDE.ADDRESS_PNT_at_MSGISPROD )
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 01 1999 - 13:10:38 CST