场景:
将b表的某些列值根据条件修改到a表中
sql如下:
UPDATE t_ucds_car_insure_test a
JOIN (
SELECT * FROM t_ucds_car_insure_middle
) b USING ( retail_total_order_num )
SET a.insurance_state = b.insurance_state,
a.sign_time = b.sign_time,
a.interest_state = b.interest_state,
a.interest_type = b.interest_type,
a.equity_package_content = b.equity_package_content,
a.operate_time = SYSDATE()
where a.on_line = 1;
解释:将b表的sign_time等列值修改到a表,将关联两表的条件放在USING中(a.retail_total_order_num = b.retail_total_order_num ),将修改a表部分记录的条件放在where上(a.on_line=1的记录被修改,其他不符合条件的不修改,因为存在多个a.retail_total_order_num 是同一个值,但是on_line是不同的)。