user3649739 user3649739 - 5 months ago 9
SQL Question

Turning a duplicate selection into an update

I've managed to select the count and IDs of each record that has duplicates via:

select T1.ID,T2.Count
from MyTable T1
join (SELECT ID,Count(*) as Count FROM MyTable
where Field_C=X
and Field_S=Y
group by NumberField,NameField having count(*)>1) T2
on T1.NumberField=T2.NumberField
and T1.NameField = T2.NameField


This returns the ID of the records I want to update (
T1.ID
) and the value I want to update a CountField with (
T2.Count
).

Just unsure how to change into update after getting this far.

Answer

If you have the select you have already the update this way

    UPDATE MyTable T1
    join (SELECT ID,Count(*) as Count FROM MyTable 
    SET T1.ID = T2.Count
    where Field_C=X 
    and Field_S=Y  
    group by NumberField,NameField having count(*)>1) T2 
    on T1.NumberField=T2.NumberField 
Comments