CODEWITHSUNDEEP
sqlsql-update
Funny Dog
Funny Dog

Reputation: 11

Update SQL column if have duplicate values

I'm trying to write a query to update my table if any value appear more than one time. Example:

table:

name value code duplicated
Jho 10 81818 false
Buo 90 81808 false
Jho 10 81818 false
May 30 81898 false

Jho appear more than one time, so I want to set each of column as duplicated= true

table:

name value code duplicated
Jho 10 81818 true
Buo 90 81808 false
Jho 10 81818 true
May 30 81898 false

I tried subquery, but it don't work:

UPDATE table SET duplicated = true
FROM (SELECT name, COUNT(*) FROM table
      GROUP BY table.name HAVING COUNT(*) > 1) as subquery
WHERE subquery.count > 1;

Any help on that?

Upvotes: 0

Views: 1491

Answers (1)

Rahul Biswas
Rahul Biswas

Reputation: 3467

Using subquery for retrieving duplicate value then using JOIN for update duplicate column as true

-- PostgreSQL (v11)
UPDATE test
SET duplicate = true
FROM (
       SELECT name t_name
            , COUNT(name) total
       FROM test
       GROUP BY name
       HAVING COUNT(name) > 1
      ) t
WHERE name = t.t_name;

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=92dfeb1b15d141e007fa49e61168e416

-- MySQL (5.6 to upper)
UPDATE test p
INNER JOIN (SELECT name t_name
                 , COUNT(name) total
            FROM test
            GROUP BY name
            HAVING COUNT(name) > 1
           ) t
       ON p.name = t.t_name
SET duplicate = true

Please check from url https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=d2571368bb55803276b4495cdff9a226

Upvotes: 1

Related Questions