Mysql multiple updates in one statement

Question

So the situation I have is this. I have 2 tables one for users and the other stores email(s) for those users. The email table is related to users by a foreign key user_id. I am trying to set up an archive system where the user can archive these users and keep them in the production db. But I need to delete the original and re-insert the user to give them a new id. This way they can archive again later and the data won't be corrupt and any new information will then be archived also. This is just a small example I am actually doing this with 10 tables, some of them related to each other. The amount of data that is involved can be quite large and when the archive starts it can take several minutes to complete because for every user I am also checking, archiving, deleting and re-inserting that user. The way I have it set up now there can be literally several 1000 queries to accomplish the complete archive.

So I am trying to re-write this so that the number of query calls is limited to one. By getting all the users, looping through them and building an insert query where I can insert many records with one call. Everything is great for the users but when I want to do this for their emails I run into an issue. As I'm looping through the users I save the id (the original id) to an array, hoping to use it later to update the new email records with the new user_id that was created. I can get the first id from my big insert and I know how many records there were so I know all the new id's. I can set up a for loop to create a new array with the new id's thereby matching the original id array.

So the question is, is there a way to set up an update statement that would allow for a multiple update statement in one call? The indexes from the two arrays will match.

update email_table
set user_id = {new_id_array}
where user_id = {old_id_array}

I have seen several different option out there but nothing that quite does what I'm trying to do. Any help is very much appreciated.

tldr. This may be helpful: https://stackoverflow.com/questions/3432/multiple-updates-in-mysql — Martin Heralecký, Feb 01 '19 at 20:50
what is the source of `{old_id_array}` and`{new_id_array}`? probably you have some table that contain it? — Alex, Feb 01 '19 at 21:03

Alex · Answer 1 · 2019-02-01T21:29:13.530

The simplest way to do what you need I think is to have some table containing old_id <-> new_id relations.

Once you have that data somewhere in your database you just need to join:

https://www.db-fiddle.com/f/bYump2wDn5n2hCxCrZemgt/1

UPDATE email_table e
JOIN replacement r
ON e.user_id = r.old_id
SET e.user_id = r.new_id;

But if you still want to do something with plain lists you need to generate query to manipulate with ELT and FIELD:

https://www.db-fiddle.com/f/noBWqJERm2t399F4jxnUJR/1

UPDATE email_table e
SET e.user_id = ELT(FIELD(e.user_id, 1, 2, 3, 5), 7, 8, 9, 10)
WHERE e.user_id IN (1,2,3,5);

Mysql multiple updates in one statement

1 Answers1