0

Say I have two tables. E.g,

Table 1:

Store Product 
 1       2    
 1       1
 2       3
 2       4

And Table 2

Store Product 
 1       2    
 2       3

How do I delete all the rows in Table 1 that are also in Table 2?

So, new Table 1 would be: 

Store Product 
 1       1
 2       4
Jacob Curtis
  • 788
  • 1
  • 8
  • 22

2 Answers2

3

You seem want :

select t1.*
from table1 t1
where not exists (select 1 
                  from table2 t2
                  where t2.store = t.store and 
                        t2.product = t1.product
                 );

Similarly delete version would be :

delete 
from table1 t1
where exists (select 1 
              from table2 t2 
              where t2.store = t.store and 
                    t2.product = t1.product
             );
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
1

You can use something like this:

DELETE
    t1
FROM
    table1 t1
WHERE
    (t1.store, t1.product) IN ( SELECT 
                                     t2.store, 
                                     t2.product 
                                from 
                                    table2 t2);
Aura
  • 1,283
  • 2
  • 16
  • 30
  • 1
    This will delete too many rows. e.g. `1 3`. Should be a *non-correlated multi-column subquery* instead: ` WHERE (store, product) IN ( SELECT store, product from table2)` – dnoeth Aug 01 '18 at 18:31
  • Thanks for the explanation @dnoeth, I updated my query with non-correlated multi-column subquery. – Aura Aug 01 '18 at 18:39