Delete unmatched records - 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 unmatched records

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-03-01 : 06:44:55

Here's my sp which lists all the records in my parent table with no relative records in my child table. I have tried to convert it a delete query to get rid of the records in tblFileRequests but keep getting a syntax error message. Can anyone put me right. TIA

CREATE PROCEDURE [spRMU_DeleteBlankRequests]

AS

SELECT tblFileRequests.MovementId, tblFileRequests.DateRequested
FROM tblFileRequests
LEFT JOIN tblFileRequestDetails ON tblFileRequests.MovementId = tblFileRequestDetails.MovementId
WHERE tblFileRequestDetails.MovementId Is Null
GO

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-01 : 07:01:37

[code]DELETE tblFileRequests
FROM tblFileRequests
LEFT JOIN tblFileRequestDetails ON tblFileRequests.MovementId = tblFileRequestDetails.MovementId
WHERE tblFileRequestDetails.MovementId Is Null[/code]

Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-01 : 08:00:36

or Use alias names

DELETE Parent
FROM tblFileRequests Parent
LEFT JOIN tblFileRequestDetails Child ON Parent.MovementId = Child.MovementId
WHERE Child.MovementId Is Null

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources