CODEWITHSUNDEEP
sqlsql-server
Zain
Zain

Reputation: 1252

Deleting using inner join

I want to delete some data from a table. I want to delete the data that is not included in the inner join

My query so far:

 DELETE *how do you say delete what wasn't included in the inner join?*
 from dbo.Cloudpie c
 inner join dbo.cake p
 on c.cakeid = p.cakeid
where cakeid > 1

Thanks in advance for all and any help provided :)!

Upvotes: 2

Views: 71

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460098

You can use a LEFT OUTER JOIN:

DELETE c
FROM dbo.Cloudpie c
LEFT OUTER JOIN dbo.cake p
    ON c.cakeid = p.cakeid
WHERE p.cakeid IS NULL AND c.cakeid > 1

or even simpler and less error-prone (on nullable columns) using NOT EXISTS:

DELETE c
FROM dbo.Cloudpie c
WHERE NOT EXISTS( SELECT 1 FROM dbo.cake p
                  WHERE p.cakeid = c.cakeid ) 
AND c.cakeid > 1

worth reading: http://www.sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join

Upvotes: 5

Related Questions