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
midrange.com
