Ask the Friendslist: Database Query

[tcepsa]

I'm trying to update one database table (A) with data from another table (B). The two tables' definitions are identical. I only want to copy a row from table B into table A if not all key fields are identical (i.e. if a row with that key does not already exist in table A). In other words if A.keyfield1 = B.keyfield1 AND A.keyfield2 = B.keyfield2, do not copy the row, but if A.keyfield1 <> B.keyfield1 OR A.keyfield2 <> B.keyfield2, copy the row. I tried doing this:

INSERT INTO A
SELECT B.keyfield1, B.keyfield2, B.otherfield
FROM B
INNER JOIN A
ON A.keyfield1 <> B.keyfield1 OR A.keyfield2 <> B.keyfield2

But no rows are copied (A is currently empty)

Plus I'm not sure that the logic is correct anyway, because I only want the data to be copied if there are NO rows for which A.keyfield1 = B.keyfield1 AND A.keyfield2 = B.keyfield2, not if there are ANY rows where A.keyfield1 <> B.keyfield1 OR A.keyfield2 <> B.keyfield2...

Comments

[shadeofnight.livejournal.com]

I had just done a lot of this exact same thing in oracle...

Does the database you use support the minus operation?

I do a lot of....

insert into a(keyfield1,keyfield2)
select keyfield1,keyfield2 from B
minus
select keyfield1,keyfield2 from A

If it is only a few fields (like less than a 1000) and performance and speed are not critical....

insert into a
select keyfield1,keyfield2 from b where keyfield1||'-'keyfield1 not in
(select keyfield1||'-'||keyfield2 from A)

If neither of these work, there are other ways, these are just two of the more simple.

[tcepsa.livejournal.com]

Unfortunately it looks like I don't have minus support. However, I re-discovered the "NOT EXISTS" condition and I think it's the answer to my problem:

INSERT INTO A (keyfield1, keyfield2, otherfield)
SELECT keyfield1, keyfield2, otherfield
FROM B
WHERE NOT EXISTS(
SELECT 1
FROM A
WHERE A.keyfield1 = B.keyfield1 AND A.keyfield2 = B.keyfield2
)