I have table users where I have column email. Some of email addresses are NULL and I would like to update these rows with some fake email address but this address must be unique (e.g. [email protected] where X will be iterated number). Could you help me how can I prepare stored procedure which will update these email addresses?
Thanks.
-
have you tried anything so far?SierraOscar– SierraOscar2017-08-07 13:49:46 +00:00Commented Aug 7, 2017 at 13:49
Add a comment
|
2 Answers
try this:
declare v_i int default 1;
declare v_rowcount int;
select count(1)
into v_rowcount
from users
where email is null;
while (v_i <= v_rowcount)
do
update users
set email = concat('test', v_i, '@example.com')
where email is null
limit 1;
set v_i = v_i + 1;
end while;
1 Comment
SMW
Perfect. Thanks. :-)
You can achieve this inside (or outside) a stored procedure as well, simply by using an all-encompassing query:
Table used for examples:
CREATE TABLE emailAddrs(email VARCHAR(255));
...
+------------+
| email |
+------------+
| NULL |
| NULL |
| NULL |
| some@email |
| NULL |
| NULL |
+------------+
Example: Using a session variable:
SET @i := 0;
UPDATE emailAddrs SET email = CONCAT(@i := @i + 1, '@example.com') WHERE email IS NULL;
Gives:
+----------------+
| email |
+----------------+
| [email protected] |
| [email protected] |
| [email protected] |
| some@email |
| [email protected] |
| [email protected] |
+----------------+
Example: Using random and universally-unique data generation:
UPDATE emailAddrs SET email = CONCAT(SHA(UUID()), '@example.com') WHERE email IS NULL;
Gives:
+------------------------------------------------------+
| email |
+------------------------------------------------------+
| [email protected] |
| [email protected] |
| [email protected] |
| some@email |
| [email protected] |
| [email protected] |
+------------------------------------------------------+
