如何在此存储过程中使用更新查询而不传递输入参数 [英] how to use update query in this stored procedure without passing input parameter

问题描述

如何在此存储过程中使用更新查询。在此（UPDATE table1 SET Total_comm =（Total_comm + TotalSalesQty WHERE ComID = ComID）其中条件列应来自select查询意味着我们不传递ComID作为输入... Pls帮助我

how to use update query in this stored procedure.In this( UPDATE table1 SET Total_comm=(Total_comm + TotalSalesQty WHERE ComID=ComID)where condition column should come from select query means we are not passing ComID as input ...Pls help me

DELIMITER $$

USE `pds`$$

DROP PROCEDURE IF EXISTS `sp_dailyReport_Test12`$$

CREATE PROCEDURE `sp_dailyReport_Test12`(IN shopno VARCHAR(20),IN frmdate DATE)
BEGIN

SELECT ts.ComID,ts.ComName,mct.Desc,ts.Total_comm,ts.TotalSalesQty, FROM table1 ts INNER JOIN table2 mct ON mct.Card=ts.Card WHERE ShopID= shopno AND DATE=frmdate;
UPDATE table1 SET Total_comm=(Total_comm + TotalSalesQty WHERE ComID=ComID
     END$$

DELIMITER ;

推荐答案

USE `pds`

DROP PROCEDURE IF EXISTS `sp_dailyReport_Test12`

DROP PROCEDURE IF EXISTS `sp_dailyReport_Test12`

CREATE PROCEDURE `sp_dailyReport_Test12`（ IN shopno VARCHAR （ 20 ）， IN frmdate DATE ）
BEGIN

SELECT ts.ComID，ts.ComName，m ct。 Desc ，ts.Total_comm，ts.TotalSalesQty， FROM table1 ts INNER JOIN table2 mct ON mct.Card = ts.Card WHERE ShopID = shopno AND DATE = frmdate;
UPDATE table1 SET Total_comm =（Total_comm + TotalSalesQty WHERE ComID = ComID
END

CREATE PROCEDURE `sp_dailyReport_Test12`(IN shopno VARCHAR(20),IN frmdate DATE) BEGIN SELECT ts.ComID,ts.ComName,mct.Desc,ts.Total_comm,ts.TotalSalesQty, FROM table1 ts INNER JOIN table2 mct ON mct.Card=ts.Card WHERE ShopID= shopno AND DATE=frmdate; UPDATE table1 SET Total_comm=(Total_comm + TotalSalesQty WHERE ComID=ComID END

这篇关于如何在此存储过程中使用更新查询而不传递输入参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！