sql - 是否可以在一个查询中将字符串附加到许多匹配的行

例如,我有以下 table:

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

我需要实现的目标:将字符串“_123”附加到“名称”为“SecondCar”、“FourthCar”或“SixthCar”的所有行的字段“名称”中,结果如下:

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

我怎样才能做到这一点?

回答1

您可以使用 https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat,它将在我们匹配的 where name in ('SecondCar','FourthCar','SixthCar') 条件下应用 _123

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

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

笔记。这将更新现有数据

回答2

使用 https://www.w3schools.com/sql/func_mysql_case.asp 语句和 https://www.w3schools.com/mysql/func_mysql_concat.asp 函数非常简单:

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

相似文章

最新文章