Re: SQL Update with Join

From: Michael Ryan

Very nice guys...that's for the help. Now, for my edification, what's
the difference between Peter's, Alan's and Guy's approaches? Peter is
selecting one, right? And Alan is selecting any, but will get the
first (or only)? And Guy is doing a straight select? What's the
difference/advantage in using the EXISTS clause? Thanks!

Peter's is the closest. Guy's will work, but will insert nulls for
non-matching records, while Alan's is missing the check for MDL to be equal.

All of them will blow up if more than one record exists in WCS for the
vendor/model combination.

Think of an update as two pieces: WHICH records do you want to update, and
WHAT do you want to update them with? You basically have two options:
update only records with a match, or stick a null into records that don't
match.

For the latter, you simply do this:

UPDATE tofile
SET tofile.tofld =
(SELECT fromfile.fromfld FROM fromfile
WHERE tofile.tokey1 = fromfile.fromkey1)

The SELECT clause tells SQL how to find the record in fromfile to get the
data from. If a match is not found, tofile.tofld will get set to NULL. If
tofld is not null-capable, an error will occur.

All versions of SQL support multiple fields in the WHERE. Most modern SQL
engines also support multiple fields in the SET and SELECT statements so
that you can update multiple fields at once.

To avoid the nulls, you have to limit the update to only those records that
have a match, which means duplicating the WHERE clause. You use the WHERE
EXISTS clause to limit the records:

UPDATE tofile
SET tofile.tofld =
(SELECT fromfile.fromfld FROM fromfile
WHERE tofile.tokey1 = fromfile.fromkey1)
WHERE EXISTS
(SELECT 1 FROM fromfile
WHERE tofile.tokey1 = fromfile.fromkey1)

The WHERE clause is now used to make sure a record exists. "SELECT 1" is an
SQL programmer's convention that says "I don't care about fields, just
return true if a record exists". In the bad old days, SELECT 1 performed
significantly better than SELECT *, but I don't know that that's the case
anymore.

In every situation I have ever used this syntax, the two WHERE clauses are
always identical. I wish the entire last three lines could be replaced by
just WHERE EXISTS, which would tell the SQL engine to not update the records
where no match is found. It's far too easy to screw this up by changing the
WHERE in one place and not the other.

Joe

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.