CODEWITHSUNDEEP
sqlt-sql
007
007

Reputation: 2186

Delete from SQL Table that has Inner Join

I have the following query.

SELECT T2.* FROM Schema.Table1 T1 INNER JOIN
Schema.Table2 T2 ON T1.ColumnName = T2.ColumnName
WHERE  T1.SomeOtherColumn = 123456

I want to delete everything from above query (everything from table 2 that has matching output based on Inner Join and Where clause. I tried the following but not working.

DELETE Schema.T2.* FROM Schema.Table2 T2 INNER JOIN 
Schema.Table1 T1 ON T1.ColumnName = T2.ColumnName 
WHERE T1.SomeOtherColumn = 123456

Please help!

EDIT:

Thank you guys. TIL FROM can be used twice without using it in subquery format. :)

Upvotes: 2

Views: 232

Answers (2)

SWeko
SWeko

Reputation: 30892

You could also rework the query to select from a single table with a subquery, like

DELETE 
FROM Table2
WHERE Table2.ColumnName = (SELECT Table1.ColumnName 
                           FROM Table1
                           WHERE Table1.SomeOtherColumn = 123456)

(note that this will work only if the subquery returns a single value, otherwise it will throw an error)

Upvotes: 3

Andomar
Andomar

Reputation: 238076

DELETE  FROM T2
FROM    Schema.Table2 T2 
JOIN    Schema.Table1 T1 
ON      T1.ColumnName = T2.ColumnName 
WHERE   T1.SomeOtherColumn = 123456

Upvotes: 9

Related Questions