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

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: [email protected]

for single update i use

UPDATE stores SET email = '[email protected]' WHERE id = 1;

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

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

CodePudding user response：

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).

CodePudding user response：

Since you are adding id to String column gmail, you can use contact() fucntion like below :

  UPDATE stors SET email=CONCAT(id, "@gmail.com") where id=2;