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

[nminusone.livejournal.com]

I think NOT EXISTS can work, but the textbook answer is to use an outer join to select the rows in B that have no matching row in A. I'm a bit fuzzy on the details but you'd probably need to do something like
"... X OUTER JOIN ON A.key1 = B.key1 and A.key2 = B.key2 where (A.key1 IS NULL) or (A.key2 IS NULL)"

Sorry, I can never remember if X should be LEFT or RIGHT. I always have to look it up or just test it. I find it easiest to just run the select without the INSERT or the WHERE clause, then go from there.