How to Delete from Two of three joined tables

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

How to Delete from Two of three joined tables

Author

Topic

magoo
Starting Member

1 Post

Posted - 2011-04-12 : 12:24:34

I have three tables that I need to join to get the record I need but after finding the record(s) I need to delete the data from two of the three tables. Is there a way to do this with a single query? What would the query look like?

Thanx in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-12 : 12:45:27

Please post the DDL for your tables (CREATE TABLE statements) along with some sample data and expected results. Anyway, the following is an example approach:

CREATE TABLE #a(a INT NOT NULL PRIMARY KEY, z VARCHAR(10) NOT NULL)
CREATE TABLE #b(b INT NOT NULL PRIMARY KEY, a INT NOT NULL)
CREATE TABLE #c(c INT NOT NULL PRIMARY KEY, b INT NOT NULL)

INSERT #a VALUES(1,'Fred')
INSERT #a VALUES(2,'Barney')
INSERT #a VALUES(3,'Mr. Slate')

INSERT #b VALUES(100,2)
INSERT #b VALUES(200,2)
INSERT #b VALUES(300,1)
INSERT #b VALUES(400,3)


INSERT #c VALUES(10,200)
INSERT #c VALUES(20,300)
INSERT #c VALUES(30,100)
INSERT #c VALUES(40,400)


SELECT * FROM #a a 
INNER JOIN #b b ON a.a=b.a
INNER JOIN #c c ON b.b=c.b

-- delete where z=Barney

SELECT b.a, b.b 
INTO #DELETE
FROM #a a 
INNER JOIN #b b ON a.a=b.a
INNER JOIN #c c ON b.b=c.b
WHERE a.z='Barney'

DELETE c
FROM #c c
INNER JOIN #DELETE d ON c.b=d.b

DELETE b
FROM #b b
INNER JOIN #DELETE d ON b.b=d.b

SELECT * FROM #a a 
INNER JOIN #b b ON a.a=b.a
INNER JOIN #c c ON b.b=c.b


DROP TABLE #a, #b, #c, #DELETE

You have to perform 2 separate DELETE statements. You'd copy the key columns you'd need for each table you want to DELETE into a separate table, so that you don't lose that data after the first DELETE statement.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-12 : 12:48:10

You could also make use of the OUTPUT clause. Here is a link that shows how to INSERT into two tables at once. You might be able to apply that to your deletes:
http://weblogs.sqlteam.com/peterl/archive/2009/07/29/How-to-insert-into-two-tables-in-one-statement.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources