CODEWITHSUNDEEP
sqlkeysql-delete
Moe
Moe

Reputation: 1051

Delete from a table without single ID

Recently, I had a problem with a query in SQL. The problem as follows: I have a 2 tables called "old" & "new". Both have the same structure and the columns "key1", "key2" and "description". Furthermore the table "new" contains a subset of the entries from "old" and additional ones.

Now I want to delete from the "old" table, all the entries which do not occur in "new". The problem is, rather "key1" nor "key2" is unique, only the combination of both.

Example entrys:

Old:

key1, key2, description
1, 2, "Hello"
1, 3, "World"
2, 3, "!"

New:

key1, key2, description
1, 3, "World"
2, 3, "!"

I tried the following sql statement, but it didn't work (at least in Access97)

DELETE
FROM old
WHERE old.key1<>new.key1 AND old.key2 <> new.key2

Does anyone have an idea on how to delete all the entries from old, which do not appear in "new" under the constraint that the combination of key1 and key2 are unique?

Upvotes: 0

Views: 107

Answers (2)

Henry Taylor
Henry Taylor

Reputation: 91

DELETE *
FROM old
WHERE old.key1 & " " & old.key2 IN (select new.key1 & " " & new.key2 from new )

Upvotes: 1

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

DELETE FROM old 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   new 
                   WHERE  old.key1 = new.key1 
                          AND old.key2 = new.key2)

You can also check for the description match

DELETE FROM old 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   new 
                   WHERE  old.key1 = new.key1 
                          AND old.key2 = new.key2 
                          AND old.description = new.description)

Upvotes: 3

Related Questions