需求:mysql4.0 想用B表中的字段的集计值(sum)=A表中的相应值,mysql4.0好像不支持子查询
mysql5.0中代码如下:
UPDATE ad_blog_spot_summary a
SET a.payment = (SELECT sum(b.payment)
FROM ad_blog_spot_disptype_summary b
WHERE spotid = 251
AND a.date = b.date)
WHERE spotid = 251
AND TO_DAYS(DATE_FORMAT('20091128','%Y%m%d')) <= TO_DAYS(a.date)
AND TO_DAYS(DATE_FORMAT('20091228','%Y%m%d')) >= TO_DAYS(a.date)mysql4.0 如何求sum的值赋给另一个表啊
mysql5.0中代码如下:
UPDATE ad_blog_spot_summary a
SET a.payment = (SELECT sum(b.payment)
FROM ad_blog_spot_disptype_summary b
WHERE spotid = 251
AND a.date = b.date)
WHERE spotid = 251
AND TO_DAYS(DATE_FORMAT('20091128','%Y%m%d')) <= TO_DAYS(a.date)
AND TO_DAYS(DATE_FORMAT('20091228','%Y%m%d')) >= TO_DAYS(a.date)mysql4.0 如何求sum的值赋给另一个表啊
试下这个:
UPDATE ad_blog_spot_summary a
inner join
(SELECT `date`,sum(b.payment) as new_payment
FROM ad_blog_spot_disptype_summary b
WHERE spotid = 251 group by `date`) c
SET a.payment = c.new_payment
on a.date = c.date
WHERE a.spotid = 251
AND TO_DAYS(DATE_FORMAT('20091128','%Y%m%d')) <= TO_DAYS(a.date)
AND TO_DAYS(DATE_FORMAT('20091228','%Y%m%d')) >= TO_DAYS(a.date)
on a.date = c.date--------------->on a.date = c.date
SET a.payment = c.new_payment
(SELECT `date`,sum(b.payment) as new_payment
FROM ad_blog_spot_disptype_summary b
WHERE spotid = 251 group by `date`) c 这个的结果存为一个表然后再操作吧