php - Updating a row based on number of existing records
Get the solution ↓↓↓
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.
Answer
Solution:
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, '@'))
Answer
Solution:
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.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: use the option --with-all-dependencies (-w) to allow upgrades, downgrades and removals for packages currently locked to specific versions.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.