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

From: <rzx2122_at_mcvax2.d48.lilly.com>
Date: 6 May 93 08:19:22 EST
Message-ID: <1993May6.081922.1_at_mcvax2.d48.lilly.com>


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                                        |        |
INOUG Committee Member                              |Indiana |
Paradigm Consulting, Inc.                 st.       | Oracle |
11080 Willowmere Drive                    louis     |  Users |
Indianapolis, IN  46280                             |   Group|
USA                                                 |        |
                                                    |      _,+cincinnati
INTERNET:   72072.2122_at_compuserve.com               /   _,'
Compuserve: 72072,2122                             /_,-'louisville
                                                   `
========================================================================
Received on Thu May 06 1993 - 15:19:22 CEST

Original text of this message