Deleting duplicate data

Hello there
I've imported table without any unique key
The table have some duplicatate data on it. and i need to delete the
duplicate data
So far i had to alter the table and add unique field, and use it to delete
the duplicate records
Is there a way to do this without altering the table?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilRoy Goldhammer wrote:
> Hello there
> I've imported table without any unique key
> The table have some duplicatate data on it. and i need to delete the
> duplicate data
> So far i had to alter the table and add unique field, and use it to delete
> the duplicate records
> Is there a way to do this without altering the table?
> --
> =F8=E5=F2=E9 =E2=E5=EC=E3=E4=EE=F8
> =F2=FA=E9=E3 =E4=F0=E3=F1=FA =FA=E5=EB=F0=E4
> =E8=EC' 03-5611606
> =F4=EC=E0' 050-7709399
> =E0=E9=EE=E9=E9=EC: roy@.atidsm.co.il
SELECT ...
INTO tmp
FROM your_table
GROUP BY keycol1, keycol2, keycol3
HAVING COUNT(*) > 1 ;
DELETE FROM your_table
WHERE EXISTS
(SELECT *
FROM tmp AS T
WHERE T.keycol1 =3D your_table.keycol1
AND T.keycol2 =3D your_tabkle.keycol2
AND T.keycol3 =3D your_tabkle.keycol3) ;
INSERT INTO yourtable (...)
SELECT ...
FROM tmp ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Roy
Please take a look at these examples written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OSv$5ruKGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hello there
> I've imported table without any unique key
> The table have some duplicatate data on it. and i need to delete the
> duplicate data
> So far i had to alter the table and add unique field, and use it to delete
> the duplicate records
> Is there a way to do this without altering the table?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||if number of rows are less than you can create temp table copy distinct
row in it, truncate main table and insert rows from temp table to main
table.
select * into temptable from (select distinct * from t1 ) a
truncate table t1
insert into t1 select * from temptable
drop table temptable.
else create identity column in the table and use it to delete data.
Regards
Amish Shahsql

DeletedFlag field and unique keys

I was thinking of using a deleted flag rather then deleting a record so that history (auditing, etc.) could be maintained. In certain tables I would like to preserve a unique key on a field (say name or code) of all non-deleted records. Is there any way to do this. I cannot have a Code + DeletedFlag field because there may be multiple records that have been deleted with the same code. I also don't want to include the deleted "codes" in the unique key because I want the user to be able to see the codes they can't use without viewing the deleted records.May be Code+DeletedFlag+DeletedDateTime could be good idea|||That means there could be duplicate codes for active entries which is what I am trying to avoid.|||You could always keep a separate table for the deleted items (with _deleted at the end of the name, or a similar convention). I do this now and then. The deleted items table wouldn't have the uniqueness constraints on it, and you could still use a view to look at the combined tables. Then slap an AFTER DELETE trigger on the original table to automatically move rows into the deleted items table.
|||

A 'better' way to handle archiving is to have separate archiving tables. Same schema, no IDENTITY fields, no constraints, index only on PK, a couple of additional columns:

ChangeBy varchar(50)DEFAULT system_user ChangeDate datetime DEFAULT getdate()|||I have thought about another table (deleted or archived) for records and that probably is the most sensible way to deal with this issue. Ideally I wanted to create an audit table and for any table I wanted to audit I would have a join table (transaction_audit for example). The audit record would hold time, user, action (cud), ip, etc. This way when looking at any record from an audited table you could see a list of actions that were performed on it. Maybe not the complete history (every changed value) which might be overkill but who did what (in general) to it when and from where. Deleted records would be kept in the database in their last state so that activity would be shown as activity and not masked.|||I agree with Arnie.

I do this all the time. Create a table exactly like the original with no identity or constraints with a few extra fields on the end, like ChangeType, UserID, ChangeDateTime, called tablename_audit. Then setup a trigger to "insert into table_audit select *, "D", @.userid, getdate() from deleted" the audit table.

The only thing you need to remember with this is, when you change the original, you MUST also change the fields in the audit log to match. This can take some time, if you have many audit records.

DeletedFlag field and unique keys

I was thinking of using a deleted flag rather then deleting a record so that history (auditing, etc.) could be maintained. In certain tables I would like to preserve a unique key on a field (say name or code) of all non-deleted records. Is there any way to do this. I cannot have a Code + DeletedFlag field because there may be multiple records that have been deleted with the same code. I also don't want to include the deleted "codes" in the unique key because I want the user to be able to see the codes they can't use without viewing the deleted records.May be Code+DeletedFlag+DeletedDateTime could be good idea|||That means there could be duplicate codes for active entries which is what I am trying to avoid.|||You could always keep a separate table for the deleted items (with _deleted at the end of the name, or a similar convention). I do this now and then. The deleted items table wouldn't have the uniqueness constraints on it, and you could still use a view to look at the combined tables. Then slap an AFTER DELETE trigger on the original table to automatically move rows into the deleted items table.
|||

A 'better' way to handle archiving is to have separate archiving tables. Same schema, no IDENTITY fields, no constraints, index only on PK, a couple of additional columns:

ChangeBy varchar(50)DEFAULT system_user ChangeDate datetime DEFAULT getdate()|||I have thought about another table (deleted or archived) for records and that probably is the most sensible way to deal with this issue. Ideally I wanted to create an audit table and for any table I wanted to audit I would have a join table (transaction_audit for example). The audit record would hold time, user, action (cud), ip, etc. This way when looking at any record from an audited table you could see a list of actions that were performed on it. Maybe not the complete history (every changed value) which might be overkill but who did what (in general) to it when and from where. Deleted records would be kept in the database in their last state so that activity would be shown as activity and not masked.|||I agree with Arnie.

I do this all the time. Create a table exactly like the original with no identity or constraints with a few extra fields on the end, like ChangeType, UserID, ChangeDateTime, called tablename_audit. Then setup a trigger to "insert into table_audit select *, "D", @.userid, getdate() from deleted" the audit table.

The only thing you need to remember with this is, when you change the original, you MUST also change the fields in the audit log to match. This can take some time, if you have many audit records.

delete multiple rows with 2 ids

Hello All,
I have a table:
idSurrogate int identity
id1 int
id2 int

id1 + id2 is a unique index

I need to delete multiple rows from the table given a list of id1 and a list of id2
In other words
@.id1List = '10,20,30'
@.id2List = '1,3,5'
I need to delete these 3 rows from the table
1) @.id1=10 and @.id2=1
2) @.id1=20 and @.id2=3
3) @.id1=30 and @.id2=5

I am a bit lazy today - can anyone help out with a delete sql stmt

Thanks!

You could use a TVF that splits the ids into individual values. You can then write a SELECT like:

delete your_table

where exists(select * from (

select t1.val, t2.val

from dbo.split_string(@.id1List) as t1

join dbo.split_string(@.id2List) as t2

on t2.idx = t1.idx /* index is column that gives position of value in the string */

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

See the link below for some ideas and samples on how to implement the TVF.

http://www.sommarskog.se/arrays-in-sql.html

Alternatively, you can modify your SP interface such that you pass the Ids as separate parameters. You need to however fix the maximum number of id pairs and this method is restricted to the number of parameters for a SP. You can then do something like:

delete your_table

where (id1 = @.id1_1 and id2 = @.id2_1)

or (id1 = @.id1_2 and id2 = @.id2_2)

or (id1 = @.id1_3 and id2 = @.id2_3)

...

-- or

delete your_table

where exists(select * from (

select @.id1_1, @.id2_1

union all

select @.id1_2, @.id2_2

...

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

|||Yes, that should do the trick.
I happen to have a 'List_To_Table' table value udf that includes an identity column.

Thanks for the help - I greatly appreciate it|||

Lazy...big time...I would never respond to this...but

Delete from MyTable

where ISEVEN(@.id1list) = TRUE

OR ISEVEN(@.idlist2) = FALSE

OR

Delete from MyTable

where @.id1 mod 10 = 0

OR @.id2 mod 10 <> 0

Write a UDF (User Defined Function)

Adamus

|||

Umachandar Jayachandran - MS wrote:

You could use a TVF that splits the ids into individual values. You can then write a SELECT like:

delete your_table

where exists(select * from (

select t1.val, t2.val

from dbo.split_string(@.id1List) as t1

join dbo.split_string(@.id2List) as t2

on t2.idx = t1.idx /* index is column that gives position of value in the string */

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

See the link below for some ideas and samples on how to implement the TVF.

http://www.sommarskog.se/arrays-in-sql.html

Alternatively, you can modify your SP interface such that you pass the Ids as separate parameters. You need to however fix the maximum number of id pairs and this method is restricted to the number of parameters for a SP. You can then do something like:

delete your_table

where (id1 = @.id1_1 and id2 = @.id2_1)

or (id1 = @.id1_2 and id2 = @.id2_2)

or (id1 = @.id1_3 and id2 = @.id2_3)

...

-- or

delete your_table

where exists(select * from (

select @.id1_1, @.id2_1

union all

select @.id1_2, @.id2_2

...

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

WTF is this garbage T-SQL101?

Adamus

|||

Adamus Turner wrote:

Lazy...big time...I would never respond to this...but

Delete from MyTable

where ISEVEN(@.id1list) = TRUE

OR ISEVEN(@.idlist2) = FALSE

OR

Delete from MyTable

where @.id1 mod 10 = 0

OR @.id2 mod 10 <> 0

Write a UDF (User Defined Function)

Adamus

Just out of quriosity, wouldnt this be just as effective:

delete from MyTable where id1 in @.idlist1 and id2 in @.idlist2|||HPEvju,
Re: Just out of quriosity, wouldnt this be just as effective:

delete from MyTable where id1 in @.idlist1 and id2 in @.idlist2

Not quite what i need
i want to delete @.id1=10 and @.id2=1
but i don't want to delete @.id1=10 and @.id2=3

Adamus ,
?
|||

Aha, didn't quite understand what you where trying to do at first. Anyways, why not pass the input as XML (assuming you are using sql 2005 that is) and do a join?

That way you can work with the data as if it was just another table.

|||

HPEvju wrote:

Aha, didn't quite understand what you where trying to do at first. Anyways, why not pass the input as XML (assuming you are using sql 2005 that is) and do a join?

That way you can work with the data as if it was just another table.

Xml would be lovely. Unfortunately, I am writing a stored procedure to accommodate a front end that passes two lists.
In other words - the front end says " hey delete these rows for me here are two ordered lists"|||

I used to do somthing like this in sql server 2000 with this user defined function:

CREATE function splitAry( @.aryTmp as varchar(8000), @.separator as char(1) = ',')
returns @.tmp table (
ident int IDENTITY (1, 1) NOT NULL ,
string varchar(35) )
AS
BEGIN
declare @.iPos int
set @.iPos = PATINDEX('%' + @.separator + '%',@.aryTmp)
WHILE LEN(@.aryTmp) > 0 and @.iPos > 0
BEGIN
set @.iPos = PATINDEX('%' + @.separator + '%',@.aryTmp)
IF @.iPos > 0
BEGIN
insert into @.tmp values(SUBSTRING(@.aryTmp,1,@.iPos-1))
set @.aryTmp = SUBSTRING(@.aryTmp,@.iPos+1,LEN(@.aryTmp)-@.iPos)
END
END
INSERT INTO @.tmp VALUES(@.aryTmp)
RETURN
END

that way you get two tables that you can easily join against.

|||HPEvju,

After reading Umachandar Jayachandran's answer, I was put on the right track and used my own udf that essentially does the same as your splitAry.

Thanks for all your help