Deleting using inner join

Question

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 :)!

Tim Schmelter · Accepted Answer · 2014-04-28 14:42:06Z

5

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

answered Apr 28, 2014 at 14:42

Tim Schmelter

462k79 gold badges718 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gordon Linoff Over a year ago

+1 . . . although you are missing the where condition.

Tim Schmelter Over a year ago

@GordonLinoff: thanks for noting, edited it to include the Where cakeid > 1.

Zain Over a year ago

I added in the where clause (I provided an example query) after he probably didn't see it :). Thank you very much tim I'll accept your answer when it lets me :).

Collectives™ on Stack Overflow

Deleting using inner join

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related