为此,将CASE语句与UPDATE命令一起使用。让我们首先创建一个表-mysql> create table DemoTable1874
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Amount varchar(100)
);
使用插入命令在表中插入一些记录-mysql> insert into DemoTable1874(Amount) values('3450');
mysql> insert into DemoTable1874(Amount) values('190');
mysql> insert into DemoTable1874(Amount) values('7600');
mysql> insert into DemoTable1874(Amount) values('4500');
使用select语句显示表中的所有记录-mysql> select * from DemoTable1874;
这将产生以下输出-+----+--------+
| Id | Amount |
+----+--------+
| 1 | 3450 |
| 2 | 190 |
| 3 | 7600 |
| 4 | 4500 |
+----+--------+
4 rows in set (0.00 sec)
这是将欧元和美元符号插入MySQL中的列的查询-mysql> UPDATE DemoTable1874 SET Amount=
CASE
WHEN Id = 1 THEN Concat('$','',Amount)
WHEN Id = 3 THEN Concat('€','',Amount)
else
Amount
END
;
Rows matched: 4 Changed: 2 Warnings: 0
让我们再次检查表记录-mysql> select * from DemoTable1874;
这将产生以下输出-+----+---------+
| Id | Amount |
+----+---------+
| 1 | $3450 |
| 2 | 190 |
| 3 | €7600 |
| 4 | 4500 |
+----+---------+
4 rows in set (0.00 sec)