anthony stoico anthony stoico - 4 years ago 98
SQL Question

Delete rows from a table "A" where column "1" values in "A" match column "1" values in table "B" but column "2" values in "A" do not exist in "B"

Sorry for the confusing title, I think my question is a bit difficult to word. I have two tables, let's call them "A" and "B" and they each have many columns. For simplicity, let's say they have just columns "1" and "2".

Now for the fun part, I want to write an SQL query that for each numeric value in column 1 of table A checks to see if the values of column 2 of table A exist in column 2 of table B when filtered by that value of column 1. And if those values in table B do not exist, delete that row in table A.

For instance, the values of row 1 in each table are ID's of objects used elsewhere. When I filter by a specific ID in each tables, I see more rows in table A than I would like, the difference is column 2, which is an associated date. I want to delete those extra rows associated with the dates that are in A and not in B when I filter for that ID.

I can't just use a NOT IN or NULL statement like the ones used here:
Delete sql rows where IDs do not have a match from another table
or here:
Delete from table if the id doesn't exists in another table
because some all the values in each column of each table do exist somewhere in the other table, just not with the corresponding filter.

This is my first time asking a question on SE and I tried my best to explain but let me know if I can provide any other info! Thanks!

Answer Source

Ad far as I can tell, you want to delete from one table if there are no corresponding values in the other. This strongly suggests not exists (regardless of the database).

As best that I can parse your description:

delete from a
    where not exists (select 1
                      from b
                      where b.col1 = a.col1 and b.col2 = a.col2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download