HOW TO DELETE RECORDS
-
hi,
My Table contains the following structure:
create table Delete_Dup_Records
(
vid int,
vName varchar(50),
vAddress varchar(40),
VGroup char(1) default 'N' not null
)
It Contains the following Records:
VID VNAME VADDRESS VGROUP
1 A A N
1 A A N
1 A A N
1 A A N
2 B C N
2 B C N
3 D C N
4 D F N
5 D K N
5 D K N
I want to delete only that records which are duplicate but there should at least one record remains in the table and output should be the following :
VID VNAME VADDRESS VGROUP
1 A A N
2 B C N
3 D C N
4 D F N
5 D K N
To achive this result i want to use simple select query.
i can't use temp_tables or identity fuctions for this.
Help me to achieve this
-
First...why can't you use temp_tables or identify functions? They are the natural solution. Create a temp table with the same structure, pass a distinct listing of the records to the temp table, delete all the records in your actual table, then copy back the records from the temp table.
If the phone doesn't ring...It's me.
-
If you don't care using a loop ... this will do
DECLARE @affected INT
SET @affected = 1
WHILE @affected > 0
BEGIN
SET ROWCOUNT 1
DELETE FROM Delete_Dup_Records
FROM Delete_Dup_Records D
INNER JOIN (SELECT vid, vName,vAddress,VGroup
FROM Delete_Dup_Records D2
GROUP BY vid, vName,vAddress,VGroup
HAVING COUNT(*) > 1) D3
ON D.vid = D3.vid
AND D.vname = D3.vname
AND D.vAddress = D3.vAddress
AND D.vGroup = D3.vGroup
SET @affected = @@ROWCOUNT
END
_/_/_/ paramind _/_/_/ -
declare @Delete_Dup_Records table
(
id_val INT IDENTITY(1,1),
vid int,
vName varchar(50),
vAddress varchar(40),
VGroup char(1) default 'N' not null
)
INSERT INTO @Delete_Dup_Records (VID, VNAME, VADDRESS, VGROUP) SELECT VID, VNAME, VADDRESS, VGROUP FROM Delete_Dup_Records
PRINT '**************'
PRINT 'BEFORE DELETE'
PRINT '**************'
SELECT VID, VNAME, VADDRESS, VGROUP FROM @Delete_Dup_Records
DELETE @Delete_Dup_Records
FROM @Delete_Dup_Records OV
WHERE EXISTS (SELECT 1 FROM @Delete_Dup_Records IV
WHERE OV.VNAME = IV.VNAME
AND OV.VADDRESS = IV.VADDRESS
AND OV.VGROUP = IV.VGROUP
AND OV.VID = IV.VID
AND OV.ID_VAL > IV.ID_VAL)
PRINT '**************'
PRINT 'AFTER DELETE'
PRINT '**************'
SELECT VID, VNAME, VADDRESS, VGROUP FROM @Delete_Dup_Records
--Output:
**************
BEFORE DELETE
**************
VID VNAME VADDRESS VGROUP
----------- -------------------------------------------------- ---------------------------------------- ------
1 A A N
1 A A N
1 A A N
1 A A N
2 B C N
2 B C N
3 D C N
4 D F N
5 D K N
5 D K N
**************
AFTER DELETE
**************
VID VNAME VADDRESS VGROUP
----------- -------------------------------------------------- ---------------------------------------- ------
1 A A N
2 B C N
3 D C N
4 D F N
5 D K N
-
or
INSERT INTO Delete_Dup_Records
SELECT
D2.vid,
D2.vName,
D2.vAddress,
'tricky' + D2.vGroup ,
FROM Delete_Dup_Records D2
GROUP BY
D2.vid,
D2.vName,
D2.vAddress,
D2.vGroup
DELETE FROM Delete_Dup_Records WHERE vGroupName NOT LIKE 'tricky%'
UPDATE Delete_Dup_Records SET vGroupName = SUBSTRING(vGroupName,7,8000)
why not complicate it the "tricky" way
_/_/_/ paramind _/_/_/ -
Yaar,
You are simply supereb.
Great !!
Regards
Amit
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply