如果行重复，我想更新列

I have the following table named information.

我有下表，名为information。

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

正如您在前一个表中看到的，列名为number，该列有许多重复的值。

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

我想要的是，如果行是重复的，将其制表单元格更新为1，否则，保持为0。

The following is what I done.

以下是我所做的。

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

2 个解决方案

#1

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.

编辑:我试过了，MySQL显示1093错误。我刚刚根据这里找到的解决方案对查询进行了编辑。

#2

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