Updating a row based on number of existing records

Question

I'm trying to update a number of rows in a user table based on a value occurring more than once. In this case it's user email - as the user can sign up to multiple websites hosted in this application.

UPDATE users SET email = REPLACE(email,'@', CONCAT('+',user_id,'@')) 
WHERE user_id IN (
  SELECT user_id FROM users HAVING COUNT('email') > 1
);

This query gives me the following error;

ERROR 1093 (HY000): You can't specify target table 'customer_entity' for update in FROM clause

I've tried a number of variations but none of these seem to work.

Does this answer your question? [You can't specify target table for update in FROM clause](https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause) or [MySQL Error 1093 - Can't specify target table for update in FROM clause](https://stackoverflow.com/a/45498/421195) — paulsm4, Jun 24 '20 at 16:31
No, because I'm doing a count of the results I can't use the join method. The subquery also doesn't work and gives; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'db.users.user_id'; this is incompatible with sql_mode=only_full_group_by — ol'bob dole, Jun 24 '20 at 16:38
The response "No" doesn't apply here ;) There are at least a dozen good suggestions between the two links I cited. Try some of them, and post back your results, along with any additional questions you might have. — paulsm4, Jun 24 '20 at 22:49
'does this answer your question?'. No is certainly a valid response. As I mentioned I've looked at both of these and tried the suggestions. Thank you anyway — ol'bob dole, Jun 25 '20 at 08:51

score 1 · Answer 1 · answered Jun 24 '20 at 16:39

1

MySQL does not support this syntax. Instead, you can self-join an aggregate query:

UPDATE users u
INNER JOIN (SELECT user_id FROM users GROUP BY user_id HAVING count(email) > 1) u1
ON u1.user_id = u.user_id
SET u.email = REPLACE(e.email,'@', CONCAT('+', u.user_id, '@'))

answered Jun 24 '20 at 16:39

GMB

216,147
25
84
135

this matches 0 rows for me – ol'bob dole Jun 25 '20 at 08:57

score 1 · Answer 2 · answered Jun 27 '20 at 06:34

The two links I cited have lots of great suggestions, and GMB's suggestion sounds promising, too.

Q: Have you really looked at each of these (multiple different!) alternatives, and tried them out yourself, with your dataset? What happened?

SUGGESTION (taking GMB's example):

Verify the select works (returns one or more rows):

SELECT user_id FROM users GROUP BY user_id HAVING count(email) > 1)

Combine the "update" with the "join" (different syntax):

UPDATE users u1
SET u1.email = REPLACE(e.email,'@', CONCAT('+', u1.user_id, '@'))
INNER JOIN users u2
  ON u1.user_id = u2.user_id
GROUP BY u2.user_id HAVING count(u2.email) > 1;

Please let us know the results.

Updating a row based on number of existing records

2 Answers2