DELETE Query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

DELETE Query

Author

Topic

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-14 : 16:24:08

Can you delete from multiple tables via a delete query?

The following errors out on me


DELETE q, qs, qf
FROM UserPortfolios up
	INNER JOIN Quotes q 
		ON (up.PortfolioID = q.PortID)
	LEFT OUTER JOIN QuoteSymbols qs
		ON (q.QuotesID = qs.QuotesID)
	LEFT OUTER JOIN QuoteFields qf
		ON (q.QuotesID = qf.QuotesID)
WHERE up.UserID = 1

would I have to turn this into 3 different delete queries as so?


DELETE qf
FROM UserPortfolios up
	INNER JOIN Quotes q 
		ON (up.PortfolioID = q.PortID)
	INNER JOIN QuoteFields qf
		ON (q.QuotesID = qf.QuotesID)
WHERE up.UserID = 1


DELETE qs
FROM UserPortfolios up
	INNER JOIN Quotes q 
		ON (up.PortfolioID = q.PortID)
	INNER JOIN QuoteSymbols qs
		ON (q.QuotesID = qs.QuotesID)
WHERE up.UserID = 1


DELETE q
FROM UserPortfolios up
	INNER JOIN Quotes q 
		ON (up.PortfolioID = q.PortID)
WHERE up.UserID = 1

Edited by - yakoo on 03/14/2002 16:32:01

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 16:27:55

Yes, only one table can be deleted at a time. If you are using SQL 2000, you can set cascading deletes if you create foreign keys between the tables. If 7.0 or earlier, you can create DELETE triggers to perform the same task; however you can't declare foreign keys if you want to do this.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-14 : 16:31:22

Thanks Rob.

I knew about the triggers and cascading of deletes with SQL 2000 but didn't have the option to modify the table structures.

Thanks for your quick reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources