oracle合并记录的用法merge

需求：把车辆状态表t_car_carstate中carstate_memo初始化为‘合格空车’，删除车辆状态表中的id大于4的车，对于没有匹配的，在insert进去。

SQL语句：

merge into t_car_carstate carstate  
using (select * from t_base_car) car
on (car.car_code=carstate.car_code)
when matched then
  update set carstate.carstate_memo='合格空车'
  delete where length(carstate.carstate_id)>4
  when not matched then
    insert (carstate.carstate_id,carstate.car_code) values (Seq_CAR_CARSTATE.nextval,car.car_code)

 

 

PS:

1.update、insert和delete都是对t_car_carstate的操作

2.update、insert只能放在matched下面