RE: SQL Gurus Wanted

From: Jon Paris

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.

Yeah, it's called RPG <grin>.

Seriously, you may not like the answer, although it works:

update A set (data1, data2) =
(select data1, data2 from B
where A.key1 = B.key1 and A.key2 = B.key2)
where exists
(select data1, data2 from B
where joe1.key1 = joe2.key1 and joe2.key2 = joe1.key2)

Note the duplication of the key clause, once to select the record to update
and once to only update those records that have matches. If you didn't do
that, you'd get NULLs in the data values for records without matches.

Also note that this will end messily if more than one record in B matches a
record in A.

Insertion is a little different:

insert into A
(select B.* from B exception join A
on A.key1 = B.key1 and A.key2 = B.key2)

This works when the tables have exactly the same layout. The "B.*" would
have to be a specific list of fields if the tables didn't match exactly.

Enjoy.

Joe

P.S. Really, though, this is almost a picture-perfect matching record
program in RPG!