× 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.



Thanks for clarifying it Joe.
That being the case, you were right on the money with the COALESCE and/or
CASE approach.

Elvis

Celebrating 10-Years of SQL Performance Excellence
http://centerfieldtechnology.com/training.asp

-----Original Message-----
Subject: RE: SQL Gurus Wanted

From: Elvis Budimlic

Enhance the WHERE clause of the base UPDATE statement (i.e. part that does
the EXISTance check), using Birgitta's example:

UPDATE TableA a
SET (a.Field1,
a.Field2, ... ,
a.FieldN) = (SELECT b.Field1, b.Field2, ... , FieldN
FROM TableB b
WHERE a.Key1 = b.Key1
and a.Key2 = b.Key2)
WHERE EXISTS (SELECT 1 FROM TableB c
WHERE a.Key1 = c.Key2
a.Key1 = c.Key2) AND
A.FIELD1 IS NOT NULL AND A.FIELD1 <> ' '

Elvis, this will only work on a single field. Jon wants to update FIELD1 if
it is NULL or blanks, and FIELD2 if it is NULL or blanks (and so on through
all the non-key fields). By adding the additional WHERE clause, it will
skip records where FIELD1 is not null or blank in file A, even if FIELD2 is
null.

That is, records where FIELD2 is NULL but FIELD1 is not will not be updated.

Joe



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.