mysql 动态拼接SQL语句求大神解答

Question

DELIMITER $$
CREATE PROCEDURE bb_HL_sum_product3
(
IN name VARCHAR(200)
)
begin

drop TABLE tmp_reasult;
declare cal1 varchar(800) ;
set cal1="
insert into tmp_reasult(产品名称,募集规模,已募额度,杠杆率,成立时间)
select product_name,project_money,PROJECT_ORDER_MONEY,extend8,setup_date
from t_product where delete_flag=0

"

if(name<>"" )
begin
#产品名称
set SQL_FOR_SELECT= concat(SQL_FOR_SELECT," "," and product_name like ","'","%name%","'")

end

PRINT cal1
EXECUTE cal1;

EXECUTE(
'update tmp_reasult set 客户总数=tmp.s1 '
+'from t_contract_order, '
+'(select count(distinct CUSTOMER_SEED) as s1,product_name from t_contract_order '
+'group by product_name )tmp where tmp_reasult.产品名称=tmp.product_name '
)

DEALLOCATE PREPARE cal1 ;

END$$

DELIMITER ;

这是我写的存储过程，但是在Navicat里面报错，估计是关于定义变量，进行拼装语句的写法有错了。请大神帮忙看看

Answer 1

update tmp_reasult set 客户总数=tmp.s1 from t_contract_order,
(select count(distinct CUSTOMER_SEED) as s1,product_name from t_contract_order
group by product_name )tmp where tmp_reasult.产品名称=tmp.product_name
mysql 不支持 update 子查询更新，你用这个试试：
update tmp_reasult a inner join t_contract_order b on a.产品名称 = b.product_name
set a.客户总数 = b.s1 where a.产品名称 = b.product_name