help with an sql statement

[lebanese007]

Hi all,
This is my first time... and i'm not an sql guy by any means, but i can insert, update, blah blah...
I have a question to ask, i have a table that has
UniqueInt | Date1 | int1 | Date2 | varchar 1

I have duplicate records that i need to delete and i need to write an sql statement.

it goes like this,

UniqueInt | Date1 | int1 | Date2 | varchar 1
1 | 1/1/2008 10:00:00 am | 1 | 12/15/2007 10:00:00 am| 123456
2 | 1/1/2008 10:00:00 am | 1 | 12/15/2007 10:30:00 am| 123456

delete the first record where int1 =1 and varchar1 is the same in both records and the difference between date2 in both records is less than 1 minute.

I know how to do the less than a minute "(DATEDIFF(minute, field1, field2) < 1)" within one record, but i don't how to do compare the same field name in 2 records

Is that possible?

Thanks in advance

[Ramy Mahrous]

I can play with SQL Server in this problem
1- define a trigger [on delete] let this trigger commit delete if deleted row > 1 and let it insert one of deleted rows into table again.

DELETE FROM table WHERE date1 in (SELECT date1 FROM table) AND varchar1 in (SELECT varchar1 FROM table

that's not the optimal solution but I think it works, I just wake up now and found this question.

[lebanese007]

Hi Ramy,
when i tried your query, it erased all the records in the table. and i don't see where i'm specifying if Date2 is less than one minute!

[Zadj]

Hi lebanese007,

If you are wanting a script that you can run to clean up the data in the table you can try the solution below. If this is an ongoing issue then it may be worth looking at putting a check during the insert to stop these additional rows. This may not be the best performance if the inserts are very frequent.

Anyway, here is a sample solution. I have simplified the table to the relevant parts only.

create table #test
(ID INT,
val INT,
txt VARCHAR(50),
dt DATETIME)

Insert into #test
Values (1, 10, 'a', '2008-1-31 12:00:00')
Insert into #test
Values (2, 10, 'a', '2008-1-31 12:00:30')
Insert into #test
Values (3, 11, 'b', '2008-1-31 12:00:00')
Insert into #test
Values (4, 10, 'a', '2008-1-31 12:02:00')

Select * from #test

DELETE FROM later --Delete the later result
FROM 
--Join the table to itself where the values match
#test later INNER JOIN
#test ealier ON later.val = ealier.val and later.txt = ealier.txt
WHERE
-- make sure the ones in the ealier table are before the ones in the later table (stops the rows matching themselves)
ealier.dt < later.dt AND
-- make sure the later date is less(or equal) than one minute from the earlier date
later.dt <= DATEADD(minute,1, ealier.dt)


Select * from #test

Drop table #test

Let me know how you get on with that.

Zadjil

[serkan sendur]

Hi all, This is my first time... and i'm not an sql guy by any means, but i can insert, update, blah blah... I have a question to ask, i have a table that has UniqueInt | Date1 | int1 | Date2 | varchar 1

I have duplicate records that i need to delete and i need to write an sql statement.

it goes like this,

UniqueInt | Date1 | int1 | Date2 | varchar 1 1 | 1/1/2008 10:00:00 am | 1 | 12/15/2007 10:00:00 am| 123456 2 | 1/1/2008 10:00:00 am | 1 | 12/15/2007 10:30:00 am| 123456

delete the first record where int1 =1 and varchar1 is the same in both records and the difference between date2 in both records is less than 1 minute.

I know how to do the less than a minute "(DATEDIFF(minute, field1, field2) < 1)" within one record, but i don't how to do compare the same field name in 2 records

Is that possible?

Thanks in advance

you can do such things using cursor as follows :

declare @varchar1 nvarchar(50)
declare mycursor cursor for
select varchar1,
from mytable
group by varchar1
having count(*) > 1

open mycursor

FETCH NEXT FROM mycursor INTO @varchar1
WHILE @@FETCH_STATUS = 0
begin

delete from mytable where (@varchar1=varchar1 and int1=1)
FETCH NEXT FROM mycursor INTO @varchar1
end

close mycursor
deallocate mycursor

[lebanese007]

Hi lebanese007,

If you are wanting a script that you can run to clean up the data in the table you can try the solution below. If this is an ongoing issue then it may be worth looking at putting a check during the insert to stop these additional rows. This may not be the best performance if the inserts are very frequent.

Anyway, here is a sample solution. I have simplified the table to the relevant parts only.

create table #test
(ID INT,
val INT,
txt VARCHAR(50),
dt DATETIME)

Insert into #test
Values (1, 10, 'a', '2008-1-31 12:00:00')
Insert into #test
Values (2, 10, 'a', '2008-1-31 12:00:30')
Insert into #test
Values (3, 11, 'b', '2008-1-31 12:00:00')
Insert into #test
Values (4, 10, 'a', '2008-1-31 12:02:00')

Select * from #test

DELETE FROM later --Delete the later result
FROM 
--Join the table to itself where the values match
#test later INNER JOIN
#test ealier ON later.val = ealier.val and later.txt = ealier.txt
WHERE
-- make sure the ones in the ealier table are before the ones in the later table (stops the rows matching themselves)
ealier.dt < later.dt AND
-- make sure the later date is less(or equal) than one minute from the earlier date
later.dt <= DATEADD(minute,1, ealier.dt)


Select * from #test

Drop table #test

Let me know how you get on with that.

Zadjil

Zadj,
thanks so much for your help, but with all that i got lost... can you please reflect my table with your code with the whole sql statement?

for your question, yes, i'm trying to clean out the table, it is not an ongoing problem anymore, so i just want to clean up this table.

Thanks alot man.

[serkan sendur]

by the way, my code erases all the rows having duplicate values in varchar1 column and value of 1 for the int1 column. if you want to delete the first row, then you have to set a break statement in the cursor as follows :

begin

delete top (1) from mytable where (@varchar1=varchar1 and int1=1)
break
FETCH NEXT FROM mycursor INTO @varchar1
end

[lebanese007]

by the way, my code erases all the rows having duplicate values in varchar1 column and value of 1 for the int1 column. if you want to delete the first row, then you have to set a break statement in the cursor as follows :

begin

delete top (1) from mytable where (@varchar1=varchar1 and int1=1) break FETCH NEXT FROM mycursor INTO @varchar1 end

Hi serkan
can you please use pure sql, i don't know what my cursor means.

Thanks in advance

[serkan sendur]

and if you want to delete only first rows of the grouped row sets, just remove the break statement from the code above as follows

begin

delete top (1) from mytable where (@varchar1=varchar1 and int1=1)

FETCH NEXT FROM mycursor INTO @varchar1
end

[serkan sendur]

Hi serkan can you please use pure sql, i don't know what my cursor means.

Thanks in advance

Cursor is a reserved keyword of t-sql for the ms sql server. Cursor is a built-in structure in ms sql's sql specification. Cursors work similar to foreach statements in c#, so if you want to do sequential operations on tables, you can use cursors. if you want to do some repeatition of commands in sql, using cursor is the best way to achieve that.

[serkan sendur]

your final sql will be as follows :

this code below deletes only first found row of row sets having the same value for the column nvarchar1 and the value of 1 for the column int1

declare @varchar1 nvarchar(50)
declare mycursor cursor for
select varchar1,
from mytable
group by varchar1
having count(*) > 1

open mycursor

FETCH NEXT FROM mycursor INTO @varchar1
WHILE @@FETCH_STATUS = 0
begin

delete top (1) from mytable where (@varchar1=varchar1 and int1=1)
FETCH NEXT FROM mycursor INTO @varchar1
end

close mycursor
deallocate mycursor

[lebanese007]

your final sql will be as follows :

this code below deletes only first found row of row sets having the same value for the column nvarchar1 and the value of 1 for the column int1

declare @varchar1 nvarchar(50) declare mycursor cursor for select varchar1, from mytable group by varchar1 having count(*) > 1

open mycursor

FETCH NEXT FROM mycursor INTO @varchar1 WHILE @@FETCH_STATUS = 0 begin

delete top (1) from mytable where (@varchar1=varchar1 and int1=1) FETCH NEXT FROM mycursor INTO @varchar1 end

close mycursor deallocate mycursor

I'm sorry Serkan but i don't know where to open mycursor! all i have is SQL enterprise manager, in there i open the table that i want, then click on sql to write my query. I haven't seen any SQL statement that has begin, end.... as i said i'm not expert by any means :(.

Thanks man.

[serkan sendur]

why dont you just copy paste the code i wrote for you? switch to sql view then paste the given code, then refresh your table, you will see the targeted records are gone.

[Zadj]

Here you go. I've changed the table structure to match your table
You should be able to copy the section that I've marked out and change the name of the table (in both positions) and it will work.

create table #YourTableName
(UniqueInt  INT,
Date1  DATETIME,
int1  INT,
Date2  DATETIME,
varchar1 VARCHAR(50)
)

Insert into #YourTableName
Values (1, '2008-1-31 12:00:00', 10, '2008-1-31 12:00:00', 'a')
Insert into #YourTableName
Values (2, '2008-1-31 12:00:00', 10, '2008-1-31 12:00:30', 'a')
Insert into #YourTableName
Values (3, '2008-1-31 12:00:00', 11, '2008-1-31 12:00:00', 'a')
Insert into #YourTableName
Values (4, '2008-1-31 12:00:00', 10, '2008-1-31 12:02:00', 'a')

Select * from #YourTableName

/* Copy from here to below - rename the #YourTableName to your own name */
DELETE FROM later --Delete the later result
FROM 
--Join the table to itself where the values match
#YourTableName later INNER JOIN
#YourTableName ealier ON later.int1 = ealier.int1 and later.varchar1 = ealier.varchar1
WHERE
-- make sure the ones in the ealier table are before the ones in the later table (stops the rows matching themselves)
ealier.Date2 < later.Date2 AND
-- make sure the later date is less(or equal) than one minute from the earlier date
later.Date2 <= DATEADD(minute,1, ealier.Date2)
/*   End copy  */

Select * from #YourTableName

Drop table #YourTableName

All the best,

Zadjil