Delete from Two tables in Single Statement (without Cascading)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Delete from Two tables in Single Statement...

Delete from Two tables in Single Statement (without Cascading)

Rate Topic

Display Mode

Topic Options

Author

Message

Mr. Kapsicum

Posted Tuesday, February 05, 2013 2:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:41 AM
Points: 121, Visits: 84

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..........!!!!! Smile

Post #1415651

Eugene Elutin

Posted Tuesday, February 05, 2013 2:31 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 2,534, Visits: 4,351

[b]
I want to delete these records from both the table in single DELETE statement. and DON'T USE CASCADING.

Need Help..............a bit urgent..........!!!!! Smile

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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1415655

Mr. Kapsicum

Posted Tuesday, February 05, 2013 2:38 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:41 AM
Points: 121, Visits: 84

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.....!!!!

Post #1415661

Eugene Elutin

Posted Tuesday, February 05, 2013 2:45 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 2,534, Visits: 4,351

Mr. Kapsicum (2/5/2013)

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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1415669

Grant Fritchey

Posted Tuesday, February 05, 2013 3:53 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 13,375, Visits: 25,158

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1415698

« Prev Topic | Next Topic »

Permissions