CODEWITHSUNDEEP
mysqlsqlsql-insert
Hugo G
Hugo G

Reputation: 16516

MySQL insert using continuous number

I'm copying a row in a table using this statement:

insert into Buyer (
    version, creationDate, password, token, username, zip, city, lastname, firstname, preferredLanguage_id, title_id, contactEmail_id, active
) select
    version, creationDate, password, token, "[email protected]", zip, city, lastname, firstname, preferredLanguage_id, title_id, contactEmail_id, active
from Buyer where username="[email protected]";

Only thing I change is the username/email. Now the number in the new username to be inserted, "[email protected]", should increment every time. So the second should be loadtest_2..., loadtest_3 and so on. I don't really care at what number it starts as long as it's continuous, so taking the ID of the newly inserted row or the like would be totally okay.

Extra kudos for ideas on how to actually create a batch of these inserts so I don't have to run it X times.

Upvotes: 0

Views: 318

Answers (2)

Rahul
Rahul

Reputation: 77896

You are selecting and inserting to same table and only change is username. what I see is you need a UPDATE statement rather like

update Buyer set username = '[email protected]'
where username="[email protected]";

If it's test and you do really want to insert test data saying [email protected] .. [email protected] then you can use a while loop like

CREATE PROCEDURE usp_inserttestdata(total INT)
AS
BEGIN
DECLARE counter INT;
DECLARE uname varchar(20);

   SET counter = 1;

   label1: WHILE counter <= total DO

    SET uname = concat('loadtest_', counter, '@example.com');     

   insert into Buyer (
    version, creationDate, password, token, username, zip, city, lastname, 
    firstname, preferredLanguage_id, title_id, contactEmail_id, active) 
   select version, creationDate, password, token, uname, zip, city, lastname, firstname, 
   preferredLanguage_id, title_id, contactEmail_id, active
   from Buyer where username="[email protected]";

   SET counter = counter + 1;

   END WHILE label1;
END

Then call the procedure saying

CALL usp_inserttestdata 1000

Upvotes: 2

fabulaspb
fabulaspb

Reputation: 1263

You can use AFTER Trigger ON INSERT operation to achive your goal. In the body of this trigger update email and set corresponding value depends on auto increment id value.

Upvotes: 0

Related Questions