Re: ?How to update one table based on another (efficiently)

rzx2122_at_mcvax2.d48.lilly.com writes:
|> Re: ?How to update one table based on another (efficiently)
|> ><CHARLESW_at_QUCDN.QueensU.CA> writes:
|> >|> I would like to update one table's records based on records in >another.
|> >|> Something like:
|> >|> UPDATE BatchTable
|> >|> SET Marked_Record = 'Y'
|> >|> WHERE ColA||ColB||ColC =
|> >|> (SELECT ColX||ColY||ColZ
|> >|> FROM AnotherTable)
|> >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);
|>
|> I started to suggest something like this. However, what if BT.ColB is NULL? No
|> records would be returned in the subquery. In Charles' solution the results
|> would be different. Without knowing the dataset, it would be difficult to
|> recommend a solution.
|>
|> The advantage of using EXISTS and an indexed subquery is that only the first
|> occurance of the subquery is returned to satisfy the condition. In Charles'
|> example, all rows in the subquery would be returned just to satisfy the WHERE
|> condition.
|>
|> The problem needs more definition before a solution can be suggested. (A
|> typical analyst remark, right?)
|>
|> ========================================================================
|> chicago ,--------+ detroit
|> Tom Harleman | |

Point well taken. To add to it, in Charles' version, Oracle will return an error if there is more than one row AnotherTable. My case would say that ('12', '34', '56') <> ('1', '2345', '6') when the concatenation  of the three would be equal which may or may not be what the original poster wanted. ;)

Roderick Manalac
Oracle Corporation
[Disclaimer: Does anybody really know what time it is?] Received on Fri May 07 1993 - 07:46:43 CEST