Combine 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

General SQL Server Forums

New to SQL Server Programming

Combine delete query

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-05-21 : 10:36:07

Hi,

I have for delete queries which I run separetly. Could I have a one statement to run instead.

DELETE FROM dbo.PatientHistory
FROM dbo.PatientHistory INNERJOIN TEST_PATS ON dbo.PatientHistory.PatientGuidDigest = TEST_PATS.PatientGuidDigest 

DELETE FROM dbo.PostcodeScores
FROM dbo.PostcodeScores INNERJOIN TEST_PATS
ON dbo.PostcodeScores.PatientGuidDigest = TEST_PATS.PatientGuidDigest

DELETE FROM dbo.Consultation
FROM dbo.Patient INNERJOIN TEST_PATS
ON dbo.Event.PatientGuidDigest = TEST_PATS.PatientGuidDigest

DELETE FROM dbo.Event
FROM dbo.Event INNERJOIN TEST_PATS
ON dbo.Patient.PatientGuidDigest = TEST_PATS.PatientGuidDigest

Thank you

GouravSaxena1987
Starting Member

23 Posts

Posted - 2014-05-21 : 11:01:52

Hello,

Please try below SQL if it works !! :

DELETE FROM dbo.PatientHistory, dbo.PostcodeScores, dbo.Consultation, dbo.Event
FROM TEST_PATS
INNER JOIN dbo.PatientHistory ON dbo.PatientHistory.PatientGuidDigest = TEST_PATS.PatientGuidDigest
INNER JOIN dbo.PostcodeScores ON dbo.PostcodeScores.PatientGuidDigest = TEST_PATS.PatientGuidDigest
INNER JOIN dbo.Event ON dbo.Patient.PatientGuidDigest = TEST_PATS.PatientGuidDigest

Regards,
Gourav Saxena
Data Warehouse Counsultant
GouravSaxena1987@gmail.com

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-21 : 11:27:18

quote:

Originally posted by dr223

Hi,

I have for delete queries which I run separetly. Could I have a one statement to run instead.

DELETE FROM dbo.PatientHistory
FROM dbo.PatientHistory INNERJOIN TEST_PATS ON dbo.PatientHistory.PatientGuidDigest = TEST_PATS.PatientGuidDigest 

DELETE FROM dbo.PostcodeScores
FROM dbo.PostcodeScores INNERJOIN TEST_PATS
ON dbo.PostcodeScores.PatientGuidDigest = TEST_PATS.PatientGuidDigest

DELETE FROM dbo.Consultation
FROM dbo.Patient INNERJOIN TEST_PATS
ON dbo.Event.PatientGuidDigest = TEST_PATS.PatientGuidDigest

DELETE FROM dbo.Event
FROM dbo.Event INNERJOIN TEST_PATS
ON dbo.Patient.PatientGuidDigest = TEST_PATS.PatientGuidDigest

Thank you

It looks like you are deleting rows from four different tables. DELETE can only remove rows from one table at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources