AW: SQL Gurus Wanted

Hi Jon,

1. Update from an other table:
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)

2. Insert from an other table
Insert into TableB
(Field1, Field2, ... , FieldN)
Select Field1, Field2, ... , FieldN
from TableA a exception join TableB b
on a.Key1 = b.Key1
and a.Key2 = b.Key2

These SQL statements are not checked, because I currently have no access to
an iSeries.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx
[mailto:midrange-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx] Im Auftrag
von Jon Paris
Gesendet: Monday, July 30, 2007 06:43
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL Gurus Wanted


Can any of the SQL gurus out there suggest the magic incantation's) that
will achieve the following.

Tables A and B have a number of common columns.

If the keys in A and B match, update columns in A with the values from B.

If there is a row in B that does not exist in A, then insert a new row based
on B.

It is late on Sunday (actually early Monday) and my brain has seized.

Jon Paris
Partner400

www.Partner400.com