How to replace time fields with MYSQL without changing hours, minutes and seconds-Mysql Tutorial-php.cn

How to replace time fields with MYSQL without changing hours, minutes and seconds

巴扎黑

Release： 2017-07-19 13:10:55

Original

1378 people have browsed it

写法1：update sas_order_supply_month_pay set RECEIVE_TIME=REPLACE(RECEIVE_TIME,DATE_FORMAT(RECEIVE_TIME,'%Y-%m-%d'),(select PERIOD_END from sas_task_supply_month_pay_period where belong='1729' and CREATE_TIME like '%2017-07-12%')) where ORDER_CODE='PO201707130115';

Copy after login

写法2：update sas_order_supply_month_pay set RECEIVE_TIME= ADDTIME ((select PERIOD_END from sas_task_supply_month_pay_period where belong='1729' and CREATE_TIME like '%2017-07-12%')+interval 0 hour,time(RECEIVE_TIME)) where ORDER_CODE='PO201707130115';

Copy after login

写法3：update sas_order_supply_month_pay set RECEIVE_TIME = concat((select PERIOD_END from sas_task_supply_month_pay_period where belong='1729' and CREATE_TIME like '%2017-07-12%'),' ',DATE_FORMAT(RECEIVE_TIME,'%H:%i:%S')) where ORDER_CODE='PO201707130115';

Copy after login

Description: The RECEIVE_TIME field format of the sas_order_supply_month_pay table is "2017-06-16 12:13:16", sas_task_supply_month_pay_period The PERIOD_END field format of the table is "2017-07-12",

After execution, RECEIVE_TIME is modified to "2017-07-12 12:13: 16".

错误写法：update sas_order_supply_month_pay set RECEIVE_TIME = DATE_FORMAT(concat((select PERIOD_END from sas_task_supply_month_pay_period where belong='1729' and CREATE_TIME like '%2017-07-12%'),' ',(select DATE_FORMAT(RECEIVE_TIME,'%H:%i:%S') from sas_order_supply_month_pay 
where ORDER_CODE='PO201707130115')),"yyyy-MM-dd %H:%i:%S") where ORDER_CODE='PO201707130115';

Copy after login

Wrong writing error: [Err] 1093 - You can't specify target table 'sas_order_supply_month_pay' for update in FROM clause

Error analysis:

Error statement:(select DATE_FORMAT(RECEIVE_TIME,'%H:%i:%S ') from sas_order_supply_month_pay where ORDER_CODE='PO201707130115')

This statement can be executed individually, but an error will be reported when executed together. Guess: the modified table and the subquery cannot be the same table. ?

The above is the detailed content of How to replace time fields with MYSQL without changing hours, minutes and seconds. For more information, please follow other related articles on the PHP Chinese website!