deleting from 3 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

deleting from 3 tables

Author

Topic

dominatio
Starting Member

2 Posts

Posted - 2008-04-22 : 06:39:00

Hello everyone,

I have to delete some rows from three different tables. My tables are:

- VERSION_1
- ID

- VERSION_2
- ID_VERSION FOREIGN KEY

- VERSION_3
- ID_VERSION FOREIGN KEY

The 3 tables are related to each other by the VERSION_1 ID.

what i have to do is deleting first from VERSION_3, then from Version_2 and finally from VERSION_1. This is what i got so far:

DELETE FROM VERSION_3, VERSION_2, VERSION_1 WHERE VERSION_3.ID_VERSION = ? AND VERSION_2.ID_VERSION = ? AND VERSION_1.ID = ?

I cannot make it work, could someone please help me? i am stuck at this.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 06:42:15

You cant fire a DELETE statement simultaneously on three tables. You need separate DELETE commands

DELETE FROM VERSION_3 WHERE {your condition}
DELETE FROM VERSION_2 WHERE {your condition}
DELETE FROM VERSION_1 WHERE {your condition}

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-22 : 06:44:49

You need to write three separate DELETE statements:

Delete v3
from VERSION_3 v3 JOIN VERSION_2 v2 on 
v3.ID_VERSION = v2.ID_VERSION
JOIN VERSION_1 v1 ON v2.ID_VERSION = v1.ID
Where <condition>

Delete v2
from VERSION_2 v2 JOIN VERSION_1 v1 ON v2.ID_VERSION = v1.ID
Where <condition>

Delete VERSION_1
Where <condition>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dominatio
Starting Member

2 Posts

Posted - 2008-04-22 : 06:44:53

quote:
Originally posted by visakh16

You cant fire a DELETE statement simultaneously on three tables. You need separate DELETE commands

DELETE FROM VERSION_3 WHERE {your condition}
DELETE FROM VERSION_2 WHERE {your condition}
DELETE FROM VERSION_1 WHERE {your condition}

Ok i will do that then, thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources