Re: SQL Update with Join -- MIDRANGE-L

Note that the VALUE_IF_NOT FOUND could be the field itself so that although an update occurs, no change takes place, i.e.

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

Of course, this takes longer since every record is updated.

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

Luis Rodriguez wrote:

Michael,

Besides the options already given, there is always the
COALESCE function, that allows you to return a value other
than NULL is the record is not found.
UPDATE tofile
SET tofile.tofld = COALESCE(
(SELECT fromfile.fromfld FROM fromfile
WHERE tofile.tokey1 = fromfile.fromkey1) , VALUE_IF_NOT FOUND)

As Joe wrote, if there is more that one record in the
subselect, the statement will abort with an error. This is
where the EXISTS clause comes in.

Regards,

Luis Rodriguez

------------------------------

message: 8
date: Thu, 10 Jan 2008 08:57:13 -0600
from: "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
subject: 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

------------------------------

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions

____________________________________________________________________________________
Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping