1

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!

Community
  • 1
  • 1
Stoic_Observer
  • 159
  • 1
  • 14
  • 1
    I removed the incompatible database tags. Please tag with the database you are really using. In addition sample data and desired results would really help your explanation. – Gordon Linoff Feb 07 '17 at 14:59
  • 1
    Add some sample table data and the expected result (as well formatted text.) Also show us your current query attempt. – jarlh Feb 07 '17 at 15:00

1 Answers1

2

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
                     );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    I was doing the same thing but "select"-ing from the wrong column. I dismissed it as me being silly, I should have posted it. Thank you for your response and input. This seems to work! – Stoic_Observer Feb 07 '17 at 15:07