I want to update a column if the row is duplicate

Question

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)");

Well, first try IN() instead of "=" (although there is considerable scope for further improvement) - and usual caveats about sql injection, deprecated functions, as well as the benefits of prepareed statements — Strawberry, Feb 05 '14 at 12:07

score 4 · Accepted Answer · edited May 23 '17 at 12:29

4

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.

edited May 23 '17 at 12:29

Community

1
1

answered Feb 05 '14 at 12:08

Iván Pérez

2,278
1
24
49

I have applied your query, but there is no update happened. `tab` column still `0`. – Lion King Feb 05 '14 at 12:19
Can you run the subquery alone and see the results? In the case of image above, it should return 2 rows (157, 300). – Iván Pérez Feb 05 '14 at 12:23
`it should return 2 rows (157, 300).`> Yes. – Lion King Feb 05 '14 at 12:28
Edited the query. The original query was raising a MySQL error due to you cannot select a table that you are updating. Please test it again, it should work now with this workaround. – Iván Pérez Feb 05 '14 at 12:30

score 1 · Answer 2 · answered Feb 05 '14 at 12:35

1

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';

answered Feb 05 '14 at 12:35

Gordon Linoff

1,242,037
58
646
786

I want to update a column if the row is duplicate

2 Answers2