CODEWITHSUNDEEP
mysql
Cyclone
Cyclone

Reputation: 15269

Set field if row field is not duplicated

I want to set the field coins to 100, in the accounts table for every row that has a unique PASSWORD.

Is that possible in MySQL?

Upvotes: 0

Views: 44

Answers (2)

jon
jon

Reputation: 6246

I think this would work:

UPDATE accounts
SET coins = 100
WHERE userid IN (
    SELECT * FROM (
        SELECT userid
        FROM accounts AS a
        GROUP BY passwd
        HAVING COUNT(passwd) = 1
        ) AS a
    )
;

Upvotes: 1

piotrm
piotrm

Reputation: 12356

UPDATE accounts a JOIN
( SELECT PASSWORD, COUNT(*) as cnt FROM accounts 
  GROUP BY PASSWORD
  HAVING cnt < 2 ) singles
ON a.PASSWORD = singles.PASSWORD
SET a.coins = 100

Upvotes: 1

Related Questions