Re: Problem with SQL updating a field from a field in another file

Below is something I had from a former coworker:

Just wanted to share some SQL "wisdom" along for updating a field using
data from another file.   Here's the example:

tempqry/benjunk1

         OUORD#     OUTIME
        238,527   10:09:57
        238,552   10:31:38
        134,203   10:40:19
        238,509   10:41:53
        238,506   10:42:42
        238,566   10:42:43
        238,035   10:42:45
        238,568   10:42:47


     tempqry/benjunk2

        OUORD#     OUTIME
       238,527   10:09:57
       238,552   10:31:38
       134,203   10:40:19
       238,506   10:42:42
       238,509   10:43:48
       220,890   10:45:16
       238,566   10:45:17
       238,100   10:45:17

My desire is to update the OUTIME in benjunk2 for order 238,509 to
10:41:53 and order 238,566 to 10:42:43.

Here's the solution:

UPDATE benjunk2 b
    SET outime =
         (SELECT a.outime FROM benjunk1 a
         WHERE a.ouord# = b.ouord# AND a.outime <> b.outime)
    WHERE ouord# IN
         (SELECT a.ouord# FROM benjunk1 a INNER JOIN benjunk2 b
         ON a.ouord# = b.ouord# AND a.outime <> b.outime)

In the end, you do need to do the inner join, but in the WHERE  clause
of the outer or primary SELECT not in the subselect or inner SELECT.
The reason the inner join is required is that you are not updating every
row in the table and the inner join
identifies those records to be updated.

Rob Berendt