mysql 查询并更新存储过程_mysql 存储过程查询插入更新操作

#UPDATE s010

UPDATE s010 SET qty=qty+(SELECT SUM(qty) from s040,s041 WHERE s040.inputid=s041.inputid AND s040.inputid=pro_inputid

AND s010.rootstockid=s040.root_stockid AND s010.pno=s041.pno AND s010.cno=s041.cno AND s010.sno=s041.sno

GROUP BY s040.root_stockid,s041.pno,s041.cno,s041.sno

), create_date=NOW();

#add s010

INSERT INTO s010 (rootstockid,pno,cno,sno,qty,create_date)

SELECT s4.root_stockid,s41.pno,s41.cno,s41.sno,SUM(s41.qty),NOW() from s040 s4,s041 s41

WHERE s4.inputid=s41.inputid AND s4.inputid=pro_inputid  AND  NOT EXISTS(SELECT * from s010 s1

WHERE s1.rootstockid=s4.root_stockid AND s1.pno=s41.pno AND s1.cno=s41.cno AND s1.sno=s41.sno)

GROUP BY s4.root_stockid,s41.pno,s41.cno,s41.sno;