April 27, 2011 at 8:15 am
Begin Tran
Declare @ID Int /* Declaring variable */
Declare eCursor CURSOR FOR /* Declaring Cursor */
Select ID from MESSAGE /* Select Stmt to declare a cursor */
WHERE
CHAR_1 IN
(
SELECT CHAR_1
FROM MESSAGE
WHERE (NOT (CHAR_1 IS NULL))
GROUP BY CHAR_1
HAVING (COUNT(*) > 1))
AND (CHAR_4 IS NULL)
Open eCursor /* Open the cursor */
Fetch Next from eCursor into @ID /* Returning the next row in the cursor into the variable*/
While @@FETCH_STATUS =0
Begin
------------------------------------------------------------------------------------------
/*Checking whether this ID is in MATTERS table,
if no such ID exists in MATTERS table then we are deleting
Record from other dependent tables and then deleting in the MESSAGE table */
IF NOT EXISTS(SELECT * FROM MATTERS AS PM WHERE PM.ID=@ID)
BEGIN
/*Deleting the record from PROFILES table*/
DELETE FROM PROFILES WHERE PROFILES.ID=@ID
/*Deleting the record from BUSINESS_UNITS table*/
DELETE FROM BUSINESS_UNITS WHERE BUSINESS_UNITS.ID=@ID
/* Deleting the record from ORGANIZATIONS table*/
DELETE FROM ORGANIZATIONS WHERE ORGANIZATIONS.ID=@ID
DELETE FROM MESSAGE WHERE PEOPLE.ID=@ID
END
-------------------------------------------------------------------------------
Fetch Next from eCursor into @ID /* Returning the next row in the cursor into the variable*/
End /* End of loop */
Close eCursor /* Closing the Cursor */
DEALLOCATE eCursor /* Deallocating Cursor */
Goal: I have to delete the duplicate records from MESSAGE table.
HOW CAN WE DO THIS SAME THIS USING 'TABLE VARIABLES'
Urgently needed advice... Please give any suggestions.....
April 27, 2011 at 8:46 am
Hi,
you could do something like this:
DECLARE @Duplicates AS TABLE (idint)
/* Create a list of duplicate ID:s that are to be removed */
INSERT INTO @Duplicates
SELECT ID FROM MESSAGE
WHERE ID NOT IN (SELECT ID FROM MATTERS) /* Leave the importand ID:s out of the list */
AND CHAR_1 IN
(SELECT CHAR_1
FROM MESSAGE
WHERE (NOT (CHAR_1 IS NULL))
GROUP BY CHAR_1
HAVING (COUNT(*) > 1)) AND (CHAR_4 IS NULL) -- Does this work? You don't group by CHAR_4!?
/*Deleting the record from PROFILES table*/
DELETE FROM PROFILES WHERE PROFILES.ID IN (SELECT id FROM @Duplicates)
/*Deleting the record from BUSINESS_UNITS table*/
DELETE FROM BUSINESS_UNITS WHERE BUSINESS_UNITS.ID IN (SELECT id FROM @Duplicates)
/* Deleting the record from ORGANIZATIONS table*/
DELETE FROM ORGANIZATIONS WHERE ORGANIZATIONS.ID IN (SELECT id FROM @Duplicates)
DELETE FROM MESSAGE WHERE PEOPLE.ID IN (SELECT id FROM @Duplicates)
Good luck!
/Markus
April 27, 2011 at 2:17 pm
Use #tempTable instead of @table variable, if you have more than 30 id's in there. Execution plans may be better, because table variables do not have statistics and optimizer assumes table variable has exactly one row, and may choose a very wrong plan if number of rows is much larger than 1.
With temp table you can execute this script on-by-one command and see which delete is problematic (missing indexes?). You can do the same with profiler and SP:StmtStarting event.
April 27, 2011 at 3:42 pm
Is there any specific reason to open a new thread every few hours for the same conceptual issue?
For example http://www.sqlservercentral.com/Forums/FindPost1099849.aspx
or http://www.sqlservercentral.com/Forums/FindPost1098387.aspx
You're just wasting other resources since we might answer in parallel with a similar solution.
Also, if the issue is urgent, get a consultant in and do not rely on forums. Especially, if you can't even wait a few hours...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply