Delete from Two tables in Single Statement (without Cascading)

Question

Delete from Two tables in Single Statement (without Cascading)

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
February 5, 2013 at 2:11 am

#270284

Table A
------------
ID NAME
1 'A'
2 'B'
3 'C'
TABLE B
------------
ID NAME
1 'A'
2 'B'
3 'C'
4 'D'
5 'E'
SAME ROW
1 'A'
2 'B'
3 'C'
I want to delete these records from both the table in single DELETE statement. and DON'T USE CASCADING.
Need Help..............a bit urgent..........!!!!! :-):-D

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

I want to delete these records from both the table in single DELETE statement. and DON'T USE CASCADING.
Need Help..............a bit urgent..........!!!!! :-):-D

It is impossible in SQL.

You can not DELETE or INSERT or UPDATE more than one table in a single statement (exclusion is using OUTPUT clause which allows to insert results of modification operation into separate table).

Actually what is the reason you want to delete from two table in single statement? If it's to maintain data consistency, than just do your deletes in single transaction.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 2

thanks sscrazy.........!!!!

it's a quick response.

There is nothing about consistency......... its just a question that someone asked me to do so..... he says its possible....!!!!

Trigger,OUTPUT, cascading are possible solution but i dont have to use none of them,,,,,,,!!! i guess MySQL has this capability......

I guess,,,,, it can be done.....!!!!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Mr. Kapsicum (2/5/2013)
thanks sscrazy.........!!!!
it's a quick response.
There is nothing about consistency......... its just a question that someone asked me to do so..... he says its possible....!!!!
Trigger,OUTPUT, cascading are possible solution but i dont have to use none of them,,,,,,,!!! i guess MySQL has this capability......
I guess,,,,, it can be done.....!!!!

That is SQL Server server forum...

Yes, you can do it in MySql.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

The only thing you can do is run the two delete statements inside a single transaction. Then, the data gets deleted, effectively, together and at the same time. But the delete statements themselves are two different deletes. As long as they're wrapped inside a transaction though, they'll complete or rollback as a single unit.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning