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

Question

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?

Possible duplicate: http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql — sgeddes, Jan 16 '13 at 14:56
@sgeddes - Not a dupe. Different RDBMS. MySQL does support a multiple table delete syntax. — Martin Smith, Jan 16 '13 at 15:16

score 2 · Accepted Answer · answered Jan 16 '13 at 15:01

2

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?

answered Jan 16 '13 at 15:01

Vivek

382
4
12

no, just interesting for my skills, thank you pretty much! I think this is even better solution. – Marius Jan 16 '13 at 15:04
1

@Marius - Not only shouldn't but can't. SQL Server doesn't have any syntax to delete from more than one table (though you can set up foreign keys with cascading deletes) – Martin Smith Jan 16 '13 at 15:14

score 2 · Answer 2 · answered Jan 16 '13 at 16:52

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 :)

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

2 Answers2