April 17, 2015 at 3:08 am
Hi Experts,
I am supposed to delete all rows from USER and its child tables based on PracticeID = '55' filter condition.
I have dynamically generated queries to delete child table first followed by parent. Table [EncounterSignOff] si child and [User] is parent.
I would like to know, whether the Query 1 is valid for deleting child records?
Query:1
DELETE Top(100000) FROM [dbo].[EncounterSignOff]
FROM [dbo].[User] INNER JOIN [dbo].[EncounterSignOff] ON [User].[UserID] = [EncounterSignOff].[UserId]
WHERE [User].PracticeID = '55';
Query:2
DELETE Top(100000) FROM [dbo].[User] WHERE [User].PracticeID = '55';
Thanks,
Naveen
April 17, 2015 at 4:14 am
--First run to check records
SELECT E.*
FROM [dbo].[EncounterSignOff] E
INNER JOIN [dbo].[User] U ON E.userID = U.UserID
WHERE U.practiceID = 55
--Run delete
DELETE E
FROM [dbo].[EncounterSignOff] E
INNER JOIN [dbo].[User] U ON E.userID = U.UserID
WHERE U.practiceID = 55
Also, you can use Foreign key constraint with on delete cascade.
April 17, 2015 at 4:22 am
Thanks Megha.
SELECT would results many thousand rows.
You have re-written the same query with alias, otherwise there is no difference between my query and yours right?
April 17, 2015 at 5:36 am
Query:1
DELETE Top(100000) FROM [dbo].[EncounterSignOff]
FROM [dbo].[User] INNER JOIN [dbo].[EncounterSignOff] ON [User].[UserID] = [EncounterSignOff].[UserId]
WHERE [User].PracticeID = '55';
your above query has two from clause, i think it will show error
April 17, 2015 at 5:49 am
It does not give error. It executes.
April 19, 2015 at 11:34 am
DELETE Top(100000) FROM [dbo].[EncounterSignOff]
FROM [dbo].[User] INNER JOIN [dbo].[EncounterSignOff] ON [User].[UserID] = [EncounterSignOff].[UserId]
WHERE [User].PracticeID = '55';
It works perfectly.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply