RE: SQL Gurus Wanted

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