How to make a multiple update on mysql with string replace with value of another column

Hello i’m new in mysql and i have to run a multiple update on my table. I have 700 records in the table and i have to update them all this way:

table example : store_id: 1 store_email: [e-mail]

for single update i use

UPDATE stores SET email = '[e-mail]' WHERE id = 1;

i need to update all the emails and replace their name with their id, so it would be like this:

[e-mail] –> [e-mail]

[e-mail] –> [e-mail]

[e-mail] –> [e-mail]

those numers have to be the ID for each store.

Hope you can understand

Thanks for help.

P.S. i need to run it on magento 2

Answer

you can use CONCAT() and RIGHT() function for manipulating strings like this:

UPDATE stores SET email = CONCAT(id, RIGHT(email, 9));

The RIGHT('string', n) function extracts n characters (storemail = 9 chars in your case) from a string (starting from right).