Re: ?How to update one table based on another (Summary)
Date: Wed, 5 May 1993 09:29:23 EDT
Message-ID: <93125.092923CHARLESW_at_QUCDN.QueensU.CA>
My thanks to everyone who responded so quickly (Daryl Erwin at Wilfred Laurier, Mark Wick at Oberon Software, Sa Nguyen at "gtegsc", and Roderick Manalac at Oracle). Here, in their own words, is what they said...
Daryl suggested:
> How about...
> UPDATE BatchTable
> SET Marked_Record = 'Y'
> WHERE EXISTS
> SELECT NULL
> FROM AnotherTable
> WHERE ColA = ColX and
> .... ....)
>
> Uses indexes on the sub-query if available - best you can get (in Oracle)?
Mark suggested:
> Just a hair more efficient, but more correct:
>
> UPDATE BatchTable
> SET Marked_Record = 'Y'
> WHERE (ColA, ColB, ColC) =
> (SELECT ColX, ColY, ColZ FROM Another_Table);
>
> The subquery here, of course, would require a WHERE clause to constrain
> the return values to one triplet (unless Another_Table only has one row).
> You can get beyond this constraint by changing the = to IN (ColA, ColB,
> ColC is a member of the set {ColX, ColY, ColZ} returned by the subquery),
> or by adding the word ANY after the = (which does the same thing).
Sa said:
> Here is something you can try
>
> Update batchtable b1
> set mark_record = 'Y'
> where exists (select 'x' from anothertable b2
> where b1.ColA = b2.ColX
> and b1.ColB = b2.ColY
> .....)
and Roderick said:
> Not sure if this is the most efficient way, but in most cases it will
> probably run faster (especially if there are indexes on the columns
> being joined):
>
> UPDATE BatchTable BT
> SET Marked_Record = 'Y'
> WHERE EXISTS
> (SELECT 1 FROM AnotherTable AT
> WHERE BT.ColA = AT.ColX
> AND BT.ColB = AT.ColY
> AND BT.ColC = AT.ColZ);
Thank you all!
.../dave Dave Charlesworth Received on Wed May 05 1993 - 15:29:23 CEST