× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.