CODEWITHSUNDEEP
phpmysqlsql
Lion King
Lion King

Reputation: 33823

I want to update a column if the row is duplicate

I have the following table named information.

enter image description here

As you see in the previous table there is column named number, this column has a many duplicate values.

What I want is, if the row is duplicate, update its tab cell to 1, otherwise, leave it as it is 0.

The following is what I done.

$query = mysql_query("UPDATE information SET tab='1' WHERE number = (SELECT distinct number FROM information)");

Upvotes: 3

Views: 75

Answers (2)

Iván Pérez
Iván Pérez

Reputation: 2539

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 duplicated number values.

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

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I would do this with an update and 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';

Upvotes: 1

Related Questions