Help with delete statement - 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

Help with delete statement

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2012-01-19 : 10:07:31

hi!

table1 and table2 have a one to many relationship, linked on sourceID

i need to delete all records from both tables, where the source status(stored in table 1) is "P"

delete from Table2 t2, Table1 t1 where t1.sourceid = t2.sourceid and t1.sourceStatus = 'P'

this does not work, please help

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-19 : 10:15:49

I'm Guessing T2 is the Child

DELETE T2 FROM Table1 T1 JOIN Table2 T2 ON T1.SourceID = T2. SourceID WHERE T1.SourceStatus = 'P'

DELETE Table1 WHERE SourceStatus = 'P'

Take a backup BEFORE you do this, or make copies of the table just in case

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-19 : 10:15:55

I don't know if it is possible to delete from two tables ion one step (without cascading delete or so...).
I would do it in two steps.
First the child table

delete t2
from table2 t2
join table1 t1 on t1.sourceid=t2.sourceid
where t1.sourceStatus='P'

Step 2
delete table1 where sourceStatus='P'

Is it what you want?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-19 : 10:16:28