CODEWITHSUNDEEP
mysql
SMW
SMW

Reputation: 145

How can I update rows in loop in MySQL?

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.

Upvotes: 2

Views: 1079

Answers (2)

wally
wally

Reputation: 3592

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] |
+------------------------------------------------------+

Upvotes: 1

ctor
ctor

Reputation: 875

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;

Upvotes: 3

Related Questions