CODEWITHSUNDEEP
sqlsql-server-2005
some_bloody_fool
some_bloody_fool

Reputation: 4685

How to delete rows in tableA which are found in tableB based on two specific columns

If i have tableA and tableB with three columns:

colA | colB | colC

And i need to look at only colA and colB

I have this so far, but it keeps deleting every row in my tableA:

DELETE

FROM
        dbA.dbo.tableA
WHERE 
        EXISTS(
                SELECT DISTINCT
                       a.colA
                       ,a.colB
                FROM
                        dbB.dbo.tableB b WITH(NOLOCK)
                            INNER JOIN dbA.dbo.tableA a WITH(NOLOCK) ON
                                b.colA = a.colA
                        AND
                                b.colB = b.colB
              )

Obviously i am doing something wrong but i have hit a wall and can't seem to figure out why it is still deleting all rows in tableA

Thank you.

Upvotes: 1

Views: 80

Answers (3)

Oleg Dok
Oleg Dok

Reputation: 21766

Try the simplified form:

DELETE dbA.dbo.tableA
FROM dbA.dbo.tableA a WITH(NOLOCK)
JOIN dbB.dbo.tableB b WITH(NOLOCK)
  ON 
      b.colA = a.colA
  AND b.colB = a.colB

AND

actually there is an error in your query near the line

                            b.colB = b.colB

which forces extra deletions from table a

AND

You can use EXISTS instead of JOIN - as mentioned in @Stefan's answer, result will be just the same but the query will be more readable

Upvotes: 4

Stefan
Stefan

Reputation: 86

DELETE FROM dbA.dbo.tableA 
WHERE EXISTS
(
  SELECT *
  FROM dbB.dbo.tableB b WITH(NOLOCK) 
  where
      b.colA = dbA.dbo.tableA .colA 
  AND b.colB = dbA.dbo.tableA .colB 
)

Upvotes: 5

Mark Shust at M.academy
Mark Shust at M.academy

Reputation: 6429

Can you possibly make it a simpler statement?

DELETE
FROM dbA.dbo.TableA a
JOIN dbA.dbo.TableB b ON b.colA = a.colA

Upvotes: 1

Related Questions