CODEWITHSUNDEEP
mysql
NeT32
NeT32

Reputation: 13

How to make this SELECT Query in UPDATE Query?

SELECT accounts.id, accounts.email FROM accounts
INNER JOIN (SELECT email FROM accounts
GROUP BY email HAVING count(id) > 5) dup ON accounts.email = dup.email

How to UPDATE only results of this query?

Upvotes: 1

Views: 48

Answers (3)

krokodilko
krokodilko

Reputation: 36107

Try:

UPDATE accounts
INNER JOIN (
  SELECT email FROM accounts
  GROUP BY email HAVING count(id) > 5) dup 
ON accounts.email = dup.email
SET active = 0

demo: http://sqlfiddle.com/#!2/a1533/1

Upvotes: 0

CodeBird
CodeBird

Reputation: 3858

You mean something like this:

UPDATE accounts SET active='0' WHERE id IN ((SELECT accounts.id FROM accounts) AS a
INNER JOIN (SELECT email FROM accounts
GROUP BY email HAVING count(id) > 5) dup ON a.accounts.email = dup.email)

Upvotes: 0

Fabio
Fabio

Reputation: 23490

I think you can achieve this with a subquery as follow

UPDATE account a
SET a.active = 0
WHERE a.id IN 
(
    SELECT accounts.id
    FROM accounts
    INNER JOIN
    (
        SELECT email 
        FROM accounts
        GROUP BY email 
        HAVING count(id) > 5
    ) dup 
    ON accounts.email = dup.email
)

Upvotes: 1

Related Questions