Deleting from several tables - 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

Deleting from several tables

Author

Topic

dowens
Yak Posting Veteran

82 Posts

Posted - 2005-08-12 : 13:19:04

How do I delete from multi tables?
There are no issues with keys.

Will this work?
DELETE MC.Document
FROM dbo.dw_MasterClaim MC, dbo.dw_MasterClaim_Checks CHK, dbo.dw_MasterClaim_ChgDate,dbo.dw_MasterClaim_Diagnosis, dbo.dw_MasterClaim_InsNo, dbo.dw_MasterClaim_Pay, dbo.dw_MasterClaim_ProcLine, dbo.dw_MasterClaim_ProcLine2, dbo.dw_MasterClaim_ProcLine3, dbo.dw_MasterClaim_RiskPool, dbo.dw_MasterClaim_SuspHist
WHERE MC.Document IN
(Select Document
FROM fl1stDwImport.dbo.raw_MasterClaim
WHERE Document = MC.Document)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-12 : 13:32:21

You need one delete statement for each table you are deleting from.

CODO ERGO SUM

dowens
Yak Posting Veteran

82 Posts

Posted - 2005-08-12 : 14:02:32

Like:

DELETE MC.Document
FROM dbo.dw_MasterClaim MC
WHERE MC.Document IN
(Select Document
FROM fl1stDwImport.dbo.raw_MasterClaim
WHERE Document = MC.Document)

DELETE CHG.Document
FROM dbo.dw_MasterClaim_ChgDate CHG
WHERE CHG.Document IN
(Select Document
FROM fl1stDwImport.dbo.raw_MasterClaim
WHERE Document = CHG.Document)

Is there a better way to?
I'm trying to remove items that have changed (incremental updates for a warehouse). Prior to adding the items back from raw.
Is there a way to replace data rather than deleting then adding it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-12 : 14:10:22

Does an UPDATE statement not work for you?

Tara

dowens
Yak Posting Veteran

82 Posts

Posted - 2005-08-12 : 14:31:41

I could however I would have to normalize the data from raw first.
My thought is to delete the items in the user table that reside in the raw table. One of the only fields in the raw that does not need to be normalize is the key, which resides in all of the tables I need to update.

Normalizing the raw data has to be done regardless.

Can you provide an example?

raclede
Posting Yak Master

180 Posts

Posted - 2005-08-17 : 01:36:02

try CASCADE Delete... see BOL..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources