CODEWITHSUNDEEP
sqlsql-server
Eric Klaus
Eric Klaus

Reputation: 955

deleting from table (join)

Suppose I've the table(t1) where column id is (1,2,3,4,5,6,7,8,9) and another table(t2) where ID column is(1,2,3,4,5).

I want to DELETE entries from table t1 so that t1 ID column would become (1,2,3,4,5)

I've tried inner join and full outer join but none of them worked out. I now this is a simple problem but I couldn't come over it. Thanks in advance

Upvotes: 0

Views: 56

Answers (2)

Johnno Nolan
Johnno Nolan

Reputation: 29659

try

DELETE from t1 where t1.ID not in (SELECT ID FROM t2)

So you didn't need to join in this case.

Upvotes: 2

Raging Bull
Raging Bull

Reputation: 18737

Try this:

DELETE FROM t1 WHERE id NOT IN (SELECT id FROM t2)

IN operator determines whether a specified value matches any value in a subquery or a list.

Syntax:

test_expression [NOT] IN 
    ( subquery | expression [ ,...n ]
    )

Read more about IN operator here.

Upvotes: 1

Related Questions