CODEWITHSUNDEEP
sqlsql-serverasp-classicsql-server-2008-r2
Dwair
Dwair

Reputation: 67

Update one duplicate record and delete the rest of them - Classic ASP & SQL Server 2008r2

I want to update one duplicate record and delete the rest of them

I have a table...

id    start unit
1      1      a
2      1      a
3      2      a
4      2      a
5      3      b

which I want to turn into... 

id    start  unit
1      1       b
3      2       b
5      3       b

I have sorted out finding the duplicates but I'm failing to be able to do anything with them - or in the example below I end up deleting all the duplicates and not keeping the first record. I'm also not convinced that this method will be the best way to approach the problem

strSQL_dup = "SELECT start_date, id 
              FROM test 
              WHERE start_date IN 
                 (SELECT start_date 
                 FROM test WHERE adId = " & adId & " AND Unit = " & scId & " 
                 GROUP BY start_date HAVING (COUNT(start_date ) > 1) ) 
                 GROUP BY test.id, start_date"
Set rsSQL_dup = conn.Execute(strSQL_dup)

Do While Not rsSQL_dup.EOF

        start_date = rsSQL_dup.Fields("start_date").value
        id = rsSQL_dup.Fields("id").value   

        'Response.Write("<p> "&start_date&" | "& id & " update me </p>"

        if bookingstate = "Provisional" Then
                    dateStatusNo = 8
                else
                    dateStatusNo = 1
                End if

                'Response.Write("<p> " & id & " update me</p>")

                strSQL_dup_update = "UPDATE test SET  Unit = '" & dateStatusNo & "' WHERE id = '" & id & "' "
                Set rsSQL_dup_update = conn.Execute(strSQL_dup_update)


        strSQL_delete = "SELECT top 1 start_date, id FROM test WHERE start_date =  " & start_date
        Set rsSQL_delete = conn.Execute(strSQL_delete)
                Do While Not rsSQL_delete.EOF
                        start_date = rsSQL_delete.Fields("start_date").value
                        id = rsSQL_delete.Fields("id").value

                        'Response.Write("<p> "&start_date&" | "& id & " delete me</p>")

                        strSQL_dup_delet = "DELETE FROM test WHERE id = '" & id & "' "
                        Set rsSQL_dup_delet = conn.Execute(strSQL_dup_delet)

        rsSQL_delete.MoveNext()  
Loop
rsSQL_dup.MoveNext()  
Loop 'End Do While Not rsSQL_dup.EOF
Loop

Upvotes: 0

Views: 543

Answers (2)

Amitesh
Amitesh

Reputation: 435

i agree with @Ken white how a change into b but if you want to remove duplicate record

why dont you use distinct command like 

select distinct start_date 
          FROM test

and if you want your query to run then you can combine it with your query like

 select distinct start_date 
          FROM test WHERE start_date IN 
             (SELECT start_date 
             FROM test WHERE adId = " & adId & " AND Unit = " & scId & " 
             GROUP BY start_date HAVING (COUNT(start_date ) > 1) ) 
             GROUP BY test.id, start_date"

and if you want to delete records then use this query 

WITH test AS(
SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
   RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
FROM dbo.Table1
)
DELETE FROM test WHERE RN > 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I'll be honest. You can probably put this together with a merge or multiple statements in a transaction. But, why not just create a temporary table, truncate the original table, and then re-insert the data?

select min(id), start, 'b' as unit
into #test
from test
group by start;

truncate table test;

insert into test(id, start, unit)
    select id, start, unit
    from #test;

drop table #test;

Upvotes: 0

Related Questions