SQL command: deleting rows based on condition in another table

Question

I am looking for the SQL command to delete rows of data from one table based on a condition in another table. Basically I'd like to delete all row data from table 1 where a userid in table 1 = a userid in table 2.

Delete from .... join ... where schould work – Jens Jul 14 '17 at 16:24 — Jens, Jul 14 '17 at 16:24

score 2 · Answer 1 · answered Jul 14 '17 at 16:25

2

I am not sure if I completly understand your question is this what you are asking for?

  DELETE Table1
   FROM Table1
   INNER JOIN Table2 ON Table1.ID = Table2.ID

Here's a link!

answered Jul 14 '17 at 16:25

Sidharth

61
1
6

In my SQL system, delete is disabled. Any ideas on workarounds not using delete? I tried a LEFT JOIN where table2.userid is not null but didn't work correctly. – Lauren Garcia Jul 14 '17 at 17:18
Never heard of disabled delete. Can you please share your code..or check the condition that you are setting. It must have to do something with that – Sidharth Jul 14 '17 at 17:22
it's the in-house tool that disables the delete function, no setting I can change. – Lauren Garcia Jul 14 '17 at 17:46
Tried `TRUNCATE` ? – Sidharth Jul 14 '17 at 18:48
Please hit the Up button if I was helpful. Peace – Sidharth Jul 14 '17 at 20:22

score 1 · Answer 2 · answered Jul 14 '17 at 16:24

1

Simply you can use following query, regardless of the SQL engine:

delete from table1 
where exists (select * from table2 where table2.userid = table1.userid)

answered Jul 14 '17 at 16:24

FLICKER

6,439
4
45
75

score 1 · Answer 3 · answered Mar 16 '22 at 15:24

I had a similar issue and was unable to resolve it using the where exists clause. For my use case that deleted all the rows in my table so beware of it, I was thankfully using a duplicate table to test this.

I had a table with duplicate values but only wanted to keep the most recent ones (had a column with the date for this). To solve my issue and to simplify my query I stored the table with the rows I wanted to delete, in a view and used that view to be the source of the rows to be deleted from the table in question.

It's essentially an adaptation from what I found here but I'll add some comments so it becomes clearer.

DELETE Table1
FROM Table1 T
INNER JOIN View1 V ON T.id = V.id 
and T.year = V.year 
and T.measure_id = V.measure_id 
and T.[date_added] != V.[date_added]

Table1 - the table you want to change both after delete and from View 1 - a view I created that contained the exact lines I wanted to delete

As you can see my example was a bit more complex (by having to use all the keys for common lines) but using a view made it simpler (maybe an idea for anyone else out there with a similar issue).

SQL command: deleting rows based on condition in another table

3 Answers3