CODEWITHSUNDEEP
sqlsql-serversql-server-2008
chobo2
chobo2

Reputation: 85845

Inner Join Delete in SQL Server 2008?

I am trying to join 2 tables together and do a delete on it.

DELETE TableA 
FROM TableA a
INNER JOIN
TableB b on b.Id = a.Id
where title like 'test'

The above is what I come up with however I keep getting

The DELETE statement conflicted with the REFERENCE constraint

I thought if I merge the 2 tables together then I will delete both at the same time and no constraints would be conflicted.

Am I missing something in my query?

Upvotes: 6

Views: 22757

Answers (3)

jyoti
jyoti

Reputation: 121

try this:

DELETE TableA 
FROM TableA 
INNER JOIN
TableB b on b.Id = TableA.Id
where TableA.title like 'test'

Upvotes: 12

Maude
Maude

Reputation: 522

I would delete one after the other with cascade constraint.

Upvotes: 0

sundar
sundar

Reputation: 1760

First try to delete TableB with that title condition Then delete those records in TableA

DELETE FROM TableB
WHERE Id IN 
( SELECT Id FROM TableA WHERE title = 'test')

DELETE FROM TableA
WHERE title = 'test'

Referential Constraints blocks you from deleting rows in TableA when you still have reference in TableB

Upvotes: 6

Related Questions