CODEWITHSUNDEEP
sqlsql-server
user5893513
user5893513

Reputation:

How to delete rows from one table matching another table?

I am trying to delete rows from one table. So this what i have done so far. I imported a .CSV file which created a temp table. I would like to delete the rows in my original table with matching with temp table.

I tried the following code :

Delete From Table1 
Where postid and userid in (Select postid, userid 
                            from Table2)

but it does not work.

The goal is to delete rows in Table 1 using Table 2.

Upvotes: 0

Views: 3201

Answers (4)

Zorkolot
Zorkolot

Reputation: 2027

You can use a Merge statement. In this case you're only interested in deletes. A semicolon is required after the delete section, followed by a final semicolon to end the merge statement. For example:

MERGE table1 AS target USING table2 AS source
ON target.postid = source.postid and target.userid = source.userid
WHEN Matched THEN DELETE;;

An example sqlfiddle: http://sqlfiddle.com/#!18/a932d/1/0

The Merge documentation: https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

Upvotes: 0

user830982
user830982

Reputation: 79

As far as I understand you want to Delete from Table1 where you want to create a composite condition based on postid and userid the from Table2 the problem is that you subquery in(Select postid,userid from Table2) is not returning the correct value as to match the in condition please modify the query to 

DELETE T1
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.postid = T2.postid
AND T1.userid = T2.userid

as stated in the previous answer

Upvotes: 0

Abdullah Dibas
Abdullah Dibas

Reputation: 1507

This is just another funny way :

DELETE FROM Table1 
WHERE STR(postid) + STR(userid) 
IN (SELECT STR(postid) + STR(userid) FROM Table2)

Upvotes: 1

Vidmantas Blazevicius
Vidmantas Blazevicius

Reputation: 4812

A simple INNER JOIN should do the job:

DELETE T1
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.postid = T2.postid
AND T1.userid = T2.userid

Upvotes: 6

Related Questions