根据B表更新A表字段问题
小弟不才，今日碰到一个表更新问题，求大神指点，建立环境如下：

--USE TEST 
--DROP TABLE A ;
--DROP TABLE B ;
CREATE TABLE A(ID VARCHAR(5),SKU VARCHAR(5),QTY NUMERIC);
CREATE TABLE B(ID VARCHAR(5),SKU VARCHAR(5),QTY NUMERIC);

INSERT INTO A
SELECT '1','abcde','1'
UNION ALL
SELECT '1','abcde','2'
UNION ALL
SELECT '1','abcde','3';

INSERT INTO B
SELECT '1','abcde','6';

SELECT * FROM A;
SELECT * FROM B;

--说明，需要根据B表QTY更新A表QTY,且更新后的SUM(A.QTY)=B.QTY
--条件是根据A.ID=B.ID ADN A.SKU=B.SKU

--举例
A表：
ID  SKU  QTY
1  ABCDE  1
1  ABCDE  2
1  ABCDE  3
B表:
1  ABCDE  5

结果：
A表：
ID  SKU  QTY
1  ABCDE  0
1  ABCDE  2
1  ABCDE  3
或者
A表：
ID  SKU  QTY
1  ABCDE  1
1  ABCDE  2
1  ABCDE  2
或者
A表：
ID  SKU  QTY
1  ABCDE  1
1  ABCDE  1
1  ABCDE  3
总之是SUM(A.QTY)=B.QTY，但是A中记录条数不能少！！！！

SQL
[解决办法]

引用:

Quote: 引用:

给你个示例

update B set B.i1=A.i1 from A where B.iid=A.iid

哥，你这是更新B表？麻烦仔细看下需求，谢谢！

你这什么需求啊？直接把A表中满足A.ID=B.ID ADN A.SKU=B.SKU第一条记录的的A.QTY改为B.QTY，其他的都是0不就可以了
[解决办法]
大概就这样，不过你的A表没有标识列，update的时候会有问题

--DROP?TABLE?A?;
--DROP?TABLE?B?;
--CREATE?TABLE?A(ID?VARCHAR(5),SKU?VARCHAR(5),QTY?int);
--CREATE?TABLE?B(ID?VARCHAR(5),SKU?VARCHAR(5),QTY?int);
?
--INSERT?INTO?A
--SELECT?'1','abcde','1'
--UNION?ALL
--SELECT?'1','abcde','2'
--UNION?ALL
--SELECT?'1','abcde','3';
?
--INSERT?INTO?B 
 
--SELECT?'1','abcde','5';
?
--SELECT?*?FROM?A;
--SELECT?*?FROM?B;
;WITH cte AS 
(
SELECT a.*,b.qty AS tqty,b.qty-a.qty AS sqty,ROW_NUMBER()OVER(ORDER BY a.qty )oid,'noneedupdate' AS [needupdate]
FROM a INNER JOIN b ON a.id=b.id AND a.sku=b.sku
),cte2 AS 
(
SELECT *
FROM cte 
WHERE oid=1
UNION ALL 
SELECT a.id,a.sku,a.qty,a.tqty,CASE WHEN b.sqty-a.qty>0 THEN b.sqty-a.qty ELSE b.sqty END sqty,a.oid,CASE WHEN b.sqty-a.qty>0 THEN 'noneedupdate' ELSE 'needupdate' END [needupdate]
FROM cte a INNER JOIN cte2 b ON a.oid=b.oid+1
)
--SELECT * FROM cte2

UPDATE a
SET a.qty=b.sqty
FROM a INNER JOIN cte2 b ON a.id=b.id AND a.sku=b.sku
WHERE b.[needupdate]='needupdate'
?
--说明，需要根据B表QTY更新A表QTY,且更新后的SUM(A.QTY)=B.QTY
--条件是根据A.ID=B.ID?ADN?A.SKU=B.SKU
?

[解决办法]

CREATE TABLE A(ID VARCHAR(5),SKU VARCHAR(5),QTY NUMERIC)
CREATE TABLE B(ID VARCHAR(5),SKU VARCHAR(5),QTY NUMERIC)

-- 测试1
INSERT INTO A
SELECT '1','abcde','1'
UNION ALL
SELECT '1','abcde','2'
UNION ALL
SELECT '1','abcde','3'
 
INSERT INTO B
SELECT '1','abcde','5'

-- 更新1
with t1 as
(select row_number() over(partition by ID,SKU order by getdate()) 'rn',
        ID,SKU,QTY from A),
t2 as
(select b.ID,b.SKU,a.QTY-b.QTY 'dq'
 from
 (select ID,SKU,QTY from B) b
 inner join
 (select ID,SKU,sum(QTY) 'QTY'
  from A group by ID,SKU) a on b.ID=a.ID and b.SKU=a.SKU
 where b.QTY<>a.QTY),
t3 as
(select a.rn,a.ID,a.SKU,a.QTY,
        isnull((select sum(b.QTY) from t1 b
                where b.ID=a.ID and b.SKU=a.SKU and b.rn<a.rn),0) 'hq'
   from t1 a
)
update t3
 set t3.QTY=case when t3.hq>=t2.dq then t3.QTY
                 when t3.hq+t3.QTY<=t2.dq then 0 
 
                 when t3.hq+t3.QTY>t2.dq then t3.QTY-(t2.dq-t3.hq) 
            end
 from t3
 inner join t2 on t3.ID=t2.ID and t3.SKU=t2.SKU

-- 结果1
SELECT * FROM A

/*
ID    SKU   QTY
----- ----- ---------------------------------------
1     abcde 0
1     abcde 2
1     abcde 3

(3 row(s) affected)
*/

SELECT * FROM B
 
/*
ID    SKU   QTY
----- ----- ---------------------------------------
1     abcde 5

(1 row(s) affected)
*/
 

-- 测试2
truncate table A
truncate table B

INSERT INTO A
SELECT '1','abcde','6'
UNION ALL
SELECT '1','abcde','2'
UNION ALL
SELECT '1','abcde','3'
 
INSERT INTO B
SELECT '1','abcde','8'

-- 更新2
with t1 as
(select row_number() over(partition by ID,SKU order by getdate()) 'rn',
        ID,SKU,QTY from A),
t2 as
(select b.ID,b.SKU,a.QTY-b.QTY 'dq'
 from
 (select ID,SKU,QTY from B) b
 inner join
 (select ID,SKU,sum(QTY) 'QTY'
  from A group by ID,SKU) a on b.ID=a.ID and b.SKU=a.SKU
 where b.QTY<>a.QTY),
t3 as
(select a.rn,a.ID,a.SKU,a.QTY,
        isnull((select sum(b.QTY) from t1 b
                where b.ID=a.ID and b.SKU=a.SKU and b.rn<a.rn),0) 'hq'
   from t1 a
)
update t3
 set t3.QTY=case when t3.hq>=t2.dq then t3.QTY
                 when t3.hq+t3.QTY<=t2.dq then 0
                 when t3.hq+t3.QTY>t2.dq then t3.QTY-(t2.dq-t3.hq) 
            end
 from t3
 inner join t2 on t3.ID=t2.ID and t3.SKU=t2.SKU 
 

-- 结果2
SELECT * FROM A

/*
ID    SKU   QTY
----- ----- ---------------------------------------
1     abcde 3
1     abcde 2
1     abcde 3

(3 row(s) affected)
*/

SELECT * FROM B
 
/*
ID    SKU   QTY
----- ----- ---------------------------------------
1     abcde 8

(1 row(s) affected)
*/

[解决办法]
MSSQL TSQL转Oracle PLSQL语法:
1. case when..then..end --> decode()
2. CTE写法(即with..as(..)) --> 临时表

[解决办法]
这种更新存在记录与记录之间的关系问题，需要循环操作，更新多条记录。
由于A表的排序规则未确定，不能得出一致的结果。
另外，A表的SUM一定得大于等于B表的值，否则也无法完成。