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)
|>
|> In other words, if AnotherTable has a triplet that matches a triplet that
|> occurs in BatchTable, then set BatchTable.Marked_Record to 'Y'.
|>
|> I can't see a more efficient way to do this in the SQL Language manual, but
|> it seems like there must be something less brutal! Can anyone give me a tip
|> on this?

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);

Roderick Manalac
Oracle Corporation
[DISCLAIMER -- YAAAAA!!! Oops that's an EXCLAIMER] Received on Wed May 05 1993 - 07:02:03 CEST