CODEWITHSUNDEEP
sqlsql-serverjoin
Marius
Marius

Reputation: 171

How I can remove rows from two tables with join in SQL Server?

Table1.

id | name
 1 | Test

Table2.

id | post | table1_id
 1 | mypst| 1

I need to remove a row from Table1 where id=1 and all rows from Table2 where table1_id=(1 is id from Table1).

I'm trying this:

DELETE FROM Table1 
INNER JOIN Table2 ON Table2.table1_id = Table1.id AND Table1.id = 1

but I'm getting error:

Incorrect syntax near the keyword 'INNER'.

What's wrong?

Upvotes: 1

Views: 3114

Answers (2)

Andrey Voloshin
Andrey Voloshin

Reputation: 345

Lets assume you are doing things right and made a foreign key (Table2.table1_id = Table1.id). Then all you need is to set ON DELETE CASCADE on Table1.

In this case everything you should do is:

DELETE FROM Table1 WHERE id = 1

...and relax. This is data consistency, dude :)

Upvotes: 2

Vivek
Vivek

Reputation: 382

You shouldn't be doing deletes from multiple tables in one query

DELETE FROM Table2 WHERE table1_id = 1;
DELETE FROM Table1 WHERE id = 1

should be fine. Is there any specific reason you wish to do it using JOIN?

Upvotes: 2

Related Questions