Updating duplicate records

Asked

Viewed 35 times

-2

Talk personal, all right? So, I made a mistake of issuing billets with the same unique number. I need to update in the table which are these duplicate records. The query that makes this identification correct and shows me duplicate records, but when updating, updates all records. I don’t understand where I’m going wrong. Thanks a lot!!!

UPDATE bol_remessa set duplicado = 'S'
WHERE bol_idassociado IN(
select t.bol_idassociado 
from(
SELECT distinct rm.bol_idassociado
FROM bol_remessa rm
GROUP BY rm.bol_idassociado
HAVING COUNT(rm.bol_numerodocumento) >1
) as t
)
mysql

  • Did you generate duplicates for just one associate? Or are there duplicates for several different associates? Another question, are there previous slips for these associates, say, from previous months correct? If yes, I believe it is enough to add a comparison with the date of issue of the tickets

1 answer

-1

Try (untested) , didn’t I use the rm.bol_numerodocument assuming to be the table’s primary key ? Tip : set bol_idassociated as UNIQUE KEY. Note : Your sql seems right , tried negative < (COUNT(*) =1) > ?

UPDATE bol_remessa set duplicado = 'S'
WHERE bol_idassociado IN (SELECT rm.bol_idassociado
                          FROM bol_remessa rm
                          GROUP BY rm.bol_idassociado
                          HAVING COUNT(*) >1)

  • Unfortunately I haven’t managed yet.

  • @Joao Marcos , what goes wrong ?

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.