以前写了个存储过程,部分内容如下:WHILE (mon <= month(now())) do
insert into jz_business_plan(g_id,gb_id,account_id) SELECT g_id,gb_id,account_id from jz_gbmes where remindtype = 2;
set mon = mon+1;
end WHILE; 现在需要先判断一下表jz_gbmes中的g_id在jz_business_plan中是否已经存在,要是不存在,再插入,还用这种insert select方法,怎么进行修改呢?谢谢!
insert into jz_business_plan(g_id,gb_id,account_id) SELECT g_id,gb_id,account_id from jz_gbmes where remindtype = 2;
set mon = mon+1;
end WHILE; 现在需要先判断一下表jz_gbmes中的g_id在jz_business_plan中是否已经存在,要是不存在,再插入,还用这种insert select方法,怎么进行修改呢?谢谢!
insert into jz_business_plan(g_id,gb_id,account_id)
SELECT a.g_id,a.gb_id,a.account_id from jz_gbmes a
left join jz_business_plan b on a.
a.g_id=b.g_id where b.g_id is null and a.remindtype = 2;
REPLACE jz_business_plan(g_id,gb_id,account_id)
SELECT g_id,gb_id,account_id from jz_gbmes where remindtype = 2;如有重复的g_id,则会被后面插入的记录更新
insert into jz_business_plan(g_id,gb_id,account_id) SELECT a.g_id,a.gb_id,a.account_id from jz_gbmes a left join jz_business_plan b on a.g_id = b.g_id where (((a.remindtype = 2) and a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now())) or ((a.remindtype = 0) and ((a.stmonth <= mon) and (a.endmonth >= mon) and (a.stday <= dayofmonth(now()) and a.enday >= dayofmonth(now()))) and ((mon+1)=a.stmonth)) or ((a.remindtype = 1) and (a.stday <= dayofmonth(now()) and dayofmonth(now())) and (((mon+1)=1) or ((mon+1)=4) or ((mon+1)=7) or ((mon+1)=10)))) and b.g_id = null;
set mon = mon+1;
end WHILE; 然后调用存储过程,没有数据插入到表jz_business_plan中。
NULL<>NULL