Inner Join Delete in SQL Server 2008?

Question

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?

Only one table is subject to delete (TableA here). This syntax is just for filtering the rows to delete. — Nikola Markovinović
– Nikola Markovinović, Commented Aug 8, 2012 at 17:15
Do you have your constraints to CASCADE DELETE? If you can add that, then it would help with these messages. — Taryn
– Taryn, Commented Aug 8, 2012 at 17:21

j0k · Accepted Answer · 2012-10-25 07:15:49Z

12

try this:

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

edited Oct 25, 2012 at 7:15

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Oct 25, 2012 at 6:56

jyoti

1211 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sundar · Accepted Answer · 2012-08-08 17:18:13Z

6

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

answered Aug 8, 2012 at 17:18

sundar

1,78012 silver badges28 bronze badges

3 Comments

chobo2 Over a year ago

Sorry. I probably should have done a.Title = 'test'. TableB has no title column. TableB is a mapping table so it just contains data to map a few tables together.

sundar Over a year ago

in my query i am looking for title column in TableA only.

chobo2 Over a year ago

Ah I see. Still getting the foreign key constraint that is between those 2 tables. I see TableB has another foreign key but it is not complaining about that one.

Maude · Accepted Answer · 2012-08-08 17:18:31Z

0

I would delete one after the other with cascade constraint.

answered Aug 8, 2012 at 17:18

Maude

5223 gold badges10 silver badges25 bronze badges

1 Comment

chobo2 Over a year ago

I was thinking of a cascade but I think I have to alter the table then and I don't want to touch the table design. If it is possible to set it on the fly then I would do it.

Collectives™ on Stack Overflow

Inner Join Delete in SQL Server 2008?

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related