更新语句,涉及三个表,怎么写呢

lxs
  • 54

update stock_spec ss SET order_num=(
SELECT SUM(actual_num)
FROM sales_trade_order sto
LEFT JOIN sales_trade st ON sto.trade_id=st.trade_id
WHERE (st.trade_status=30 or st.trade_status=35) and sto.spec_id=ss.spec_id
)

上面是我写的,只是为了描述清楚问题,不能在mysql中实现更新。
就是要更新stock_spec表中order_num为sales_trade_order中所有spec_id等于ss表中spec_id的actual_num的和同时要求sales_trade表的status等于30或35

回复
阅读 2.4k
5 个回答
RECTOR
  • 660
✓ 已被采纳

看了你的问题,但不清楚你的业务表的具体表结构,把重要的字段提取出来,作了个DEMO,不知道是不是你问题里面想要的结果(温馨提示一下:下次提问的问题还是把你的表结构和测试用例写出来吧),以下是测试表结构和数据:

CREATE DATABASE `test`;

USE `test`;

DROP TABLE IF EXISTS `sales_trade`;

CREATE TABLE `sales_trade` (
  `id` int(11) DEFAULT NULL,
  `trade_id` varchar(16) DEFAULT NULL,
  `trade_status` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `sales_trade`(`id`,`trade_id`,`trade_status`) values 
(1,'order_num_001',30),
(2,'order_num_002',35);

DROP TABLE IF EXISTS `sales_trade_order`;

CREATE TABLE `sales_trade_order` (
  `id` int(11) DEFAULT NULL,
  `trade_id` varchar(16) DEFAULT NULL,
  `spec_id` int(11) DEFAULT NULL,
  `actual_num` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `sales_trade_order`(`id`,`trade_id`,`spec_id`,`actual_num`) values 
(1,'order_num_002',1,5),
(2,'order_num_003',3,10),
(3,'order_num_001',3,2);


DROP TABLE IF EXISTS `stock_spec`;

CREATE TABLE `stock_spec` (
  `id` int(11) DEFAULT NULL,
  `trade_id` varchar(16) DEFAULT NULL,
  `order_num` int(11) DEFAULT NULL,
  `spec_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `stock_spec`(`id`,`trade_id`,`order_num`,`spec_id`) values 
(1,'order_num_001',0,1),
(2,'order_num_002',0,1),
(3,'order_num_003',0,3);

以下是更新操作:

UPDATE stock_spec AS ss 
INNER JOIN (
    SELECT sto.spec_id,SUM(actual_num) AS total_num 
    FROM sales_trade_order AS sto 
    LEFT JOIN sales_trade AS st ON sto.trade_id=st.trade_id
    WHERE (st.trade_status=30 OR st.trade_status=35)
    GROUP BY sto.spec_id
) AS t
SET ss.order_num=t.total_num 
WHERE ss.spec_id=t.spec_id

Before updated:

图片描述

After updated:

图片描述

其实我也没看懂,但我知道用SUM的话必定应该用GROUP BY的。

最简单的是分三条一起执行啊 加个begin end;就好了

把需要执行的update语句分成每张表的update语句,然后放在事务里执行,这样应该是最简单的方法了吧

update stock_spec ss SET order_num=(
    SELECT SUM(actual_num) FROM sales_trade_order sto where sto.trade_id in (select trade_id from sales_trade st where (st.trade_status=30 or st.trade_status=35)) and sto.spec_id=ss.spec_id);
宣传栏