DELETE FROM with a JOIN - 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

DELETE FROM with a JOIN

Author

Topic

samtwilliams
Starting Member

18 Posts

Posted - 2010-07-05 : 05:52:21

Hi All,

I have been trying to work out how to delete with joins but i can't seem to get it to work.

I have two tables table1 and table2 joined by a KITNUMBER.

I want to delete everything that is more than two hours old from table1 and for any child data to be removed from table2 as well.

Would appreciate some assiatance.

Many Thanks
Sam

My STATEMENT so far;

DELETE FROM table1, table2
JOIN table2
ON table2.KITNUMBER = table1.KITNUMBER
WHERE table1.DATECREATED < DATEADD(hh,-2,GetDate())

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-05 : 06:18:17

[code]DELETE t1
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 AS t2 ON t2.KitNumber = t1.KitNumber
WHERE t1.DateCreated < DATEADD(HOUR, -2, GETDATE())[/code]

N 56°04'39.26"
E 12°55'05.63"

samtwilliams
Starting Member

18 Posts

Posted - 2010-07-05 : 07:57:53

This still leaves data within t2. When it should be removing all child entries?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-05 : 08:09:11

If you are using DELETE as cascading action, they are removed at the same time.

N 56°04'39.26"
E 12°55'05.63"

beniaminus
Starting Member

21 Posts

Posted - 2010-07-05 : 08:13:56

Peso is quite right. But only if you've set up your tables with the right primary/foreign keys.

Failing that you'll have to run two delete statements. One to delete the relevant rows in the child table, and the second to delete from the parent table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources