当前表更新字段
同一个表中的字段更新,其后接条件
update t_order set PRICE = PAY_PRICE where id is not null;
联查更新
更新字段时,可使用联查语句进行条件拼接
UPDATE t_user u INNER JOIN t_order o ON u.ORDER_ID = o.ID
SET u.STATUS = o.STATUS where o.other_STATUS > 2 and u.STATE = 1;
查询插入
目标表按照指定字段,插入查询到的数据(可多表联查,需注意字段顺序)
INSERT INTO t_product_info (`ID`,`ORDER_ID`,`INSERT_USER`,`INSERT_TIME`,`UPDATE_USER`,`UPDATE_TIME`,`DELETE_FLAG`)
select REPLACE(UUID(),'-','') as ID,o.ID as ORDER_ID,o.INSERT_USER,o.INSERT_TIME,e.UPDATE_USER,o.UPDATE_TIME,o.DELETE_FLAG
from t_order o INNER JOIN t_order_exq e on e.ORDER_ID = o.id
where o.STATUS = 8;
暂时仅记录工作中用到的场景,仅供参考。未完待续…