MYSQL replacement time (year month day) Field hour minute second does not change instance resolution, mysql minute second
Statement 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';
Statement 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';
Statement 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';
Note: s
The RECEIVE_TIME field of the as_order_supply_month_pay table is in the format of "12:13:16", and the PERIOD_END field of the sas_task_supply_month_pay_period table is in the format ",
After executionRECEIVE_TIME
Change"2017-07-12 12:13:16"
.
Incorrect syntax:
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';
The following error occurs when writing an 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 independently, but an error is reported when it is executed together. guess: the table and subquery cannot be modified in the same table?
The above is the implementation method of the MYSQL replacement time (year, month, day) field with the same time, minutes, And seconds. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!