mysql - 根据单元格值更新 MySql 表

Question

我在下面有一个包含各种列的表格。我想根据其他字段的值更新某些字段的值。

我的 table 在这里

DROP TABLE IF EXISTS my_table; 
CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT UNIQUE,
Shot VARCHAR(4),
lay VARCHAR(15) NOT NULL,
lay_avaibility VARCHAR(15) NOT NULL,
blk VARCHAR(10) NOT NULL,
blk_avaibility VARCHAR(15) NOT NULL,
pri VARCHAR(10) NOT NULL,
pri_avaibility VARCHAR(15) NOT NULL,
ani VARCHAR(10) NOT NULL,
ani_avaibility VARCHAR(15) NOT NULL,
status VARCHAR(5)
);

INSERT INTO my_table VALUES
(1,'SH01','1863','Offline','1863','Offline','P4645','Offline','P4557','Offline','Over'),
(2,'SH02','1863','Offline','P4645','Offline','P4557','Offline','1863','Offline','Over')
(3,'SH03','P4645','Offline','P4557','Offline','1863','Offline','1863','Offline','WIP'),
(4,'SH04','1863','Offline','P4645','Offline','P4557','Offline','1863','Offline','RTK'),
(5,'SH05','1863','Offline','1863','Offline','P4645','Offline','P4557','Offline','WIP'),
(6,'SH06','P4557','Offline','P4645','Offline','P4645','Offline','P4557','Offline','WIP');

SELECT * FROM my_table;

+----+------+-------+----------------+-------+----------------+-------+----------------+-------+----------------+--------+
| id | Shot | lay   | lay_avaibility | blk   | blk_avaibility | pri   | pri_avaibility | ani   | ani_avaibility | status |
+----+------+-------+----------------+-------+----------------+-------+----------------+-------+----------------+--------+
|  1 | SH01 | 1863  | Offline        | 1863  | Offline        | P4645 | Offline        | P4557 | Offline        | Over   |
|  2 | SH02 | 1863  | Offline        | P4645 | Offline        | P4557 | Offline        | 1863  | Offline        | Over   |
|  3 | SH03 | P4645 | Offline        | P4557 | Offline        | 1863  | Offline        | 1863  | Offline        | WIP    |
|  4 | SH04 | 1863  | Offline        | P4645 | Offline        | P4557 | Offline        | 1863  | Offline        | RTK    |
|  5 | SH05 | 1863  | Offline        | 1863  | Offline        | P4645 | Offline        | P4557 | Offline        | WIP    |
|  6 | SH06 | P4557 | Offline        | P4645 | Offline        | P4645 | Offline        | P4557 | Offline        | WIP    |
+----+------+-------+----------------+-------+----------------+-------+----------------+-------+----------------+--------+
6 rows in set (0.00 sec)

SELECT VERSION();

+-----------+
| VERSION() |
+-----------+
| 5.5.23    |
+-----------+
1 row in set (0.02 sec)

我想将所有 lay_avability、blk_avability、pri_avability、ani_avability 列更新为“在线”，其中 lay、pri、blk、ani 列的值为“1863”，即最终表格应如下所示

+----+------+-------+----------------+-------+----------------+-------+----------------+-------+----------------+--------+
| id | Shot | lay   | lay_avaibility | blk   | blk_avaibility | pri   | pri_avaibility | ani   | ani_avaibility | status |
+----+------+-------+----------------+-------+----------------+-------+----------------+-------+----------------+--------+
|  1 | SH01 | 1863  | Online         | 1863  | Online         | P4645 | Offline        | P4557 | Offline        | Over   |
|  2 | SH02 | 1863  | Online         | P4645 | Offline        | P4557 | Offline        | 1863  | Online         | Over   |
|  3 | SH03 | P4645 | Offline        | P4557 | Offline        | 1863  | Online         | 1863  | Online         | WIP    |
|  4 | SH04 | 1863  | Online         | P4645 | Offline        | P4557 | Offline        | 1863  | Online         | RTK    |
|  5 | SH05 | 1863  | Online         | 1863  | Online         | P4645 | Offline        | P4557 | Offline        | WIP    |
|  6 | SH06 | P4557 | Offline        | P4645 | Offline        | P4645 | Offline        | P4557 | Offline        | WIP    |
+----+------+-------+----------------+-------+----------------+-------+----------------+-------+----------------+--------+

Answer 1

您应该能够将这样的 CASE 语句与更新命令一起使用:

UPDATE my_table 
    SET lay_availability = CASE WHEN lay = '1863' THEN 'Online' ELSE lay_availability END,
    blk_availability = CASE WHEN blk = '1863' THEN 'Online' ELSE blk_availability END,
    pri_availability = CASE WHEN pri = '1863' THEN 'Online' ELSE pri_availability END,
    ani_avaibility   = CASE WHEN ani = '1863' THEN 'Online' ELSE ani_avaibility END;