如果"name"和"address_1"有重复的条目,我正在尝试更新一个 MySQL 表以将列名称"已处理"显示为"2",但它不起作用 - 像往常一样,我认为我只是有点白痴。
这是我正在尝试的
UPDATE `records`
SET `processed`='2', `count` = (SELECT COUNT(`user`)
FROM `records`
WHERE `name`<>''
AND `address_1`<>'')
WHERE `count`=> '1';
基本上,如果有多个"名称"和"address_1",则"已处理"字段需要更新为"2"。
您可以使用如下所示的查询来返回重复的名称和地址:
SELECT name, address_1, COUNT(*) cnt
FROM records
GROUP BY name, address_1
HAVING COUNT(*)>1
,然后将此查询联接到记录表,并将处理的列更新为 2,其中联接成功:
UPDATE
records INNER JOIN (SELECT name, address_1, COUNT(*) cnt
FROM records
GROUP BY name, address_1
HAVING COUNT(*)>1) duplicates
ON records.name = duplicates.name
AND records.address_1=duplicates.address_1
SET
`processed`='2',
`count` = duplicates.cnt
WHERE
records.`name`<>''
AND records.`address_1`<>''