Re: SQL Gurus Wanted

Elvis,
Thanks for the link to the info on SQL.
Are these newsletters available to all, or only users of the Centerfield products?

Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2
IBM Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3









----- Original Message ----
From: Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Monday, July 30, 2007 11:46:58 AM
Subject: RE: SQL Gurus Wanted


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 <> ' '

Check out the "Anatomy of a joined UPDATE" article on page 9 in this
Centerfield newsletter for a 400 centric SQL UPDATE illustration:

http://www.centerfieldtechnology.com/publications/archive%5CDecember%202006.
pdf

HTH, Elvis.

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

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

Yeah, it's called RPG <grin>.

Yup - I wholeheartedly agree Joe. It is just that I keep hearing that SQL
is the answer to everything so I was trying to see how to use it here. It
is fundamentally a very simple task and yet I just couldn't see how to make
it work.

I'm still not seeing how your or Birgitta's solution identifies _which_
columns in A are empty (i.e. they are blanks or nulls) and only update them
from B when they are currently empty. But I'll try to get my head around it
this morning.

Thanks to both yourself and Birgitta.

Jon Paris