Geek asks and answers

I want to update a column if the row is duplicated

I have the following table with a name information.

enter image description here

As you see in the previous table a column with a name number, this column has many duplicate values.

I want if the row repeats, update its cell tabto 1, otherwise leave it as 0.

The following is what I did.

$query = mysql_query("UPDATE information SET tab='1' WHERE number = (SELECT distinct number FROM information)");
+3
source share
2 answers
UPDATE information
SET tab = '1'
WHERE number IN (SELECT number
                 FROM (SELECT number
                       FROM information
                       GROUP BY number
                       HAVING count(*) > 1
                      ) AS temp)

The subquery will return all duplicate values number.

Edit: I tried and MySQL shows error 1093. I just edited the query according to the solution I found here .

+4
source

update join:

UPDATE information i join
       (select number
        from information i
        group by number
        having count(*) > 1
       ) idups
       on i.number = idups.number
    SET i.tab = '1';
+1

More articles:


All Articles