Multi-table Delete? - 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

Multi-table Delete?

Author

Topic

TheKai
Starting Member

16 Posts

Posted - 2011-05-14 : 00:05:13

Hi there. I'm trying to delete related records from several tables. Based on some reading, I thought the following query would work, but it's no good. Any ideas?

I want to delete all records, that are marked as IsTemp=1 in the tblMaster table and all related records from the other tables, matching on the ID field of the tblMaster table.

DELETE
tblMaster, -- query analyzer complains about this comma
PropertiesFloat,
PropertiesInt,
PropertiesLong,
PropertiesString,
Stats
FROM
tblMaster
PropertiesFloat,
PropertiesInt,
PropertiesLong,
PropertiesString,
Stats
WHERE
tblMaster.IsTemp = 1
AND
PropertiesFloat.ID = tblMaster.ID
AND
PropertiesInt.ID = tblMaster.ID
AND
tblPropertiesLong.ID = tblMaster.ID
AND
tblPropertiesString.ID = tblMaster.ID
AND
tblStats.ID = tblMaster.ID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-14 : 00:24:03

You can't. You have to do it table by table

KH
[spoiler]Time is always against us[/spoiler]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-14 : 23:35:04

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

What you did post looks like a nightmare of EAV. Google it.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-15 : 04:28:07

All DML statements (insert, update, delete) must target a single table per statement. Delete from the child tables first, then from the parent.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources