写法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';
写法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';
写法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';
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';
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!