CODEWITHSUNDEEP
mysqlsql
JTAG
JTAG

Reputation: 1

How I can update multiple rows mysql?

I have this datatable:

    id, surname, name;
    1, Ivanov, Ivan;
    2, Petrov, Petr;
    3, Sidorov, Sidr;

I want to change: Ivan to Ivan111, Petr to Petr222, Sidr to Sidr333.

How I can do this in one query? something like: 

`UPDATE datatable SET name='Ivan111', name='Petr222', name=Sidr333' WHERE id=1, id=2, id=3`

Upvotes: 0

Views: 72

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You can use CASE statement to perform conditional update

UPDATE datatable 
SET name= CASE  WHEN  id=1 THEN 'Ivan111'
                WHEN  id=2 THEN 'Petr222'
                WHEN  id=3 THEN 'Sidr333'
                ELSE  name 
          END

or if you want to concatenate id part 3 times with name for all records you can do it like

For select 

select id, surname, concat(name,repeat(id, 3)) as name from datatable

For update

update datatable set name = concat(name,repeat(id, 3)) ;

Upvotes: 3

huanglufei
huanglufei

Reputation: 11

update datatable set name = CONCAT（name,id,id）

Upvotes: 0

Related Questions