Is it possible to append string to many matching rows in one query-CodePudding

For an example, i have following table:

Id	Name	Color
1	FirstCar	White
2	SecondCar	Yellow
3	ThirdCar	Red
4	FourthCar	White
5	FifthCar	Green
6	SixthCar	Blue

What i need to achieve: append string "_123" to field "Name" for all rows where "Name" is "SecondCar", "FourthCar" or "SixthCar" resulting following:

Id	Name	Color
1	FirstCar	White
2	SecondCar_123	Yellow
3	ThirdCar	Red
4	FourthCar_123	White
5	FifthCar	Green
6	SixthCar_123	Blue

How can i do this?

CodePudding user response：

You can use CONCAT, which will apply _123 at our matching where name in ('SecondCar','FourthCar','SixthCar') condition .

UPDATE test set name = CONCAT(name,'_123') 
WHERE name IN ('SecondCar','FourthCar','SixthCar');

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fd790f9f30fecdb2f3dc22cb2d8ca0a5

Note. This will update existing data

CodePudding user response：

It's quite straightforward by using a CASE WHEN statement together with a CONCAT function:

SELECT
  id,
  CASE
    WHEN name IN ("SecondCar", "FourthCar", "SixthCar")
      THEN CONCAT(name, "_123")
    ELSE name
  END AS name,
  color
FROM your_table;