根据另一列中的DATE值执行MySQL UPDATE
让我们首先创建一个表-
mysql> create table DemoTable( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(100), AdmissionDate date );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable(Name,AdmissionDate) values('Chris','2019-11-21');
mysql> insert into DemoTable(Name,AdmissionDate) values('Mike','2019-03-11');
mysql> insert into DemoTable(Name,AdmissionDate) values('Sam','2018-04-01');
mysql> insert into DemoTable(Name,AdmissionDate) values('Carol','2019-05-01');使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+----+-------+---------------+ | Id | Name | AdmissionDate | +----+-------+---------------+ | 1 | Chris | 2019-11-21 | | 2 | Mike | 2019-03-11 | | 3 | Sam | 2018-04-01 | | 4 | Carol | 2019-05-01 | +----+-------+---------------+ 4 rows in set (0.00 sec)
现在让我们基于特定的DATE值（即2019-04-01此处）更新表-
mysql> update DemoTable set Name='Robert',AdmissionDate='2019-04-01' where Id=3; Rows matched: 1 Changed: 1 Warnings: 0
让我们再次检查表记录-
mysql> select *from DemoTable;
这将产生以下输出-
+----+--------+---------------+ | Id | Name | AdmissionDate | +----+--------+---------------+ | 1 | Chris | 2019-11-21 | | 2 | Mike | 2019-03-11 | | 3 | Robert | 2019-04-01 | | 4 | Carol | 2019-05-01 | +----+--------+---------------+ 4 rows in set (0.00 sec)