Delete from SQL table where ID is found from conditions applied to another table

Question

First of all, I don't know if the title is right but let me show you what I want and I will correct it as suggested. So, I have 2 tables:

• table1
  • ID, subid, name
• table2
  • ID

What I want is to delete any element from table2 that has ID equal to subid in table1, where table1.name is equal to a specified value.

If I have these elements in table1

ID    subid    name
1 ... 1 ...... name1  
2 ... 3 ...... name2  
3 ... 2 ...... name1  
4 ... 1 ...... name2  

and these rows in table2

ID
1
2
3
4

I would like to remove those elements in table2 with ID = subid, when name = name1, which means elements 1 and 2.

Something like:

DELETE FROM table2
WHERE  ID = (SELECT subid
             FROM   table1
             WHERE  NAME = "name1") 

Is this possible?

Answer 1

You were very close.

You just need = ANY rather than = as the subquery can return more than one row SQL Fiddle.

DELETE 
FROM table2
WHERE  ID = ANY (SELECT t1.subid
                 FROM   table1 t1
                 WHERE  t1.name = 'name1') 

Though this is more commonly expressed using IN

DELETE 
FROM table2
WHERE  ID IN (SELECT t1.subid
              FROM   table1 t1
              WHERE  t1.name = 'name1') 

A couple of other changes I made to your posted query...

1. I always ensure that column references in subqueries use two part names to avoid unfortunate surprises.
2. You should use single quotes to delimit string literals so it works under default QUOTED_IDENTIFIER settings and is not interpreted as referencing a column called name1.

Answer 2

You can delete using joins as well, so yup very possible.

You can identify the (to be) deleted records first with:

select t2.*
from table2 t2
inner join table1 t1 on t2.id = t1.subId
    and  t1.name = 'whatever'

then perform the delete as such:

delete t2
from table2 t2
inner join table1 t1 on t2.id = t1.subId
   and t1.name = 'whatever'

@eckes see my fiddle with the syntax that I'm using to see it works: http://sqlfiddle.com/#!6/260a5