CODEWITHSUNDEEP
mysqlsql
Elliot Reed
Elliot Reed

Reputation: 749

MySQL - Update with COUNT, is it possible?

I'm attempting to update a MySQL table to show column name 'processed' as '2' if there is duplicate entries for 'name' and 'address_1', but it's not working - as usual I think I'm just being a bit of a moron..

Here's what I'm trying

UPDATE `records`
SET `processed`='2', `count` = (SELECT COUNT(`user`)
FROM `records`
WHERE `name`<>''
AND `address_1`<>'')
WHERE `count`=> '1';

Basically, if there's more than one 'name' and 'address_1' then the 'processed' field needs updating to '2'..

Upvotes: 0

Views: 85

Answers (1)

fthiella
fthiella

Reputation: 49049

You could use a query like this one to return duplicated names and addresses:

SELECT name, address_1, COUNT(*) cnt
FROM records
GROUP BY name, address_1
HAVING COUNT(*)>1

and then join this query to the records table, and update the column processed to 2 where the join succeeds:

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

Upvotes: 1

Related Questions