RE: SQL Gurus Wanted

From: Porterfield, Sean

Is the WHERE EXISTS import for every update from one table to another or
only when changing or joining on multiple fields? This is exactly the
kind of query I was trying to locate last week, but I am only changing one
field and joining on one field.

UPDATE File1
SET File1.Data1 = File2.Data1
FROM File1, File2
WHERE File1.Key1 = File2.Key1

Should this work correctly? Thanks.

Sean, as far as I know the syntax above won't work at all. You must use a
subselect to get the data from File2. At the minimum, you would do this:

update A set (data1, data2) =
(select data1, data2 from B
where A.key1 = B.key1 and A.key2 = B.key2)

However, without the WHERE EXISTS, the query will update all records in A.
Those without matching keys in B will get NULLS, since there is no source
data. It doesn't just skip records (a clause like "UPDATE MATCHING ONLY"
would be nice, but doesn't exist in SQL).

So you have to limit the target using the WHERE EXISTS:

update A set (data1, data2) =
(select data1, data2 from B
where A.key1 = B.key1 and A.key2 = B.key2)
where exists
(select data1, data2 from B
where A.key1 = B.key1 and A.key2 = B.key2)

Note that you can make the WHERE EXISTS a little simpler. You don't
actually have to specify the fields on the SELECT for the WHERE EXISTS:

where exists
(select 1 from B
where A.key1 = B.key1 and A.key2 = B.key2)

For older SQL engines, this may actually run a little faster, although not
necessarily.

Joe