CODEWITHSUNDEEP
sqlsql-delete
John Smith
John Smith

Reputation: 69

Special delete query

I've got two tables:

I need to delete data which not contains id from tags table.

Example:

tags: 1, 2
tags_news: 2

There is no data with id=1 in tags_news. And I need to delete this. I don't know how. Please, help me.

Upvotes: 0

Views: 71

Answers (3)

Christian Phillips
Christian Phillips

Reputation: 18749

You can use a Left join too, see below

DECLARE @tags TABLE ( id INT )
DECLARE @tags_news TABLE ( id INT )

INSERT  INTO @tags
        ( id )
VALUES  ( 1 )
INSERT  INTO @tags
        ( id )
VALUES  ( 2 )
INSERT  INTO @tags
        ( id )
VALUES  ( 3 )
INSERT  INTO @tags_news
        ( id )
VALUES  ( 2 )

DELETE  t
FROM    @tags AS t
        LEFT JOIN @tags_news tn ON t.id = tn.id
WHERE   tn.id IS NULL

SELECT  id
FROM    @tags

Upvotes: 0

jongusmoe
jongusmoe

Reputation: 676

delete from tags where id not in(select id from tags_new)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460038

You can use NOT EXISTS

DELETE tn FROM dbo.TagsNews tn
WHERE NOT EXISTS(
    SELECT 1 FROM dbo.Tags t
    WHERE t.ID = tn.ID
)

Upvotes: 1

Related Questions