我需要更新字段tableA,其结果来自tableA销售组的查询select SUM(按日期)
我试过像这样
update tableA set Total=(按日期从销售组选择sum(billamt) )
它显示返回的错误子查询超过一个值。当子查询跟随=、!=、<、<=、>、>=或子查询用作表达式时,这是不允许的。
发布于 2014-10-29 06:24:06
试一试如下:
update A
set Total = B.Total
From tableA A
Inner Join (select DATE,RNAME,sum(billamt) Total from SALES group by DATE,RNAME) B
On A.Date = B.Date and A.RName = B.RNAme发布于 2014-10-29 05:56:16
查询:
select sum(billamt) from SALES group by DATE,RNAME返回sum of billamt,按DATE,RNAME分组。
如果有多个DATE或RNAME唯一值,则返回的值是一个表(而不是一个字段)。
您可以单独运行此查询并检查返回的值。
由于错误状态,您不能将多个值设置为一个字段。
发布于 2014-10-29 06:28:38
由于表结构在上面的问题中不清楚,所以我假设tableA有总计、日期和RName列。
update tableA set tableA.total =
a.total from (select SUM(amt) as total, date, rname from SALES group by DATE,RNAME) as a
where a.date = tableA.date and tableA.rname = a.rname-编辑“Rname可以在sales表中为null”使用
update tableA set tableA.total = a.total
from (select SUM(amt) as total, date, rname from #t1 group by DATE,RNAME) as a
where a.date = tableA.date and tableA.rname = a.rname
OR (a.date = tableA.date AND tableA.rname IS NULL and a.rname IS NULL)-进一步拟订
CREATE TABLE #t1 (amt decimal(18,2), [date] date, rname varchar(50))
INSERT INTO #t1
SELECT 1.9, GETDATE() - 1, NULL
UNION ALL
SELECT 1.9, GETDATE() - 1, NULL
UNION ALL
SELECT 8.9, GETDATE() - 1, NULL
UNION ALL
SELECT 8.9, GETDATE(), NULL
UNION ALL
SELECT 2.9, GETDATE() - 1, 'N1'
UNION ALL
SELECT 2.8, GETDATE() - 1, 'N1'
CREATE TABLE #t2 (total decimal(18,2), [date] date, rname varchar(50))
INSERT INTO #t2
SELECT 0, GETDATE() - 1, NULL
UNION ALL
SELECT 0, GETDATE() - 1, 'N1'
update #t2 set #t2.total = a.total
from (select SUM(amt) as total, date, rname from #t1 group by DATE,RNAME) as a
where a.date = #t2.date and #t2.rname = a.rname
OR (a.date = #t2.date AND #t2.rname IS NULL and a.rname IS NULL)
SELECT * FROM #t2 我希望现在安全了
https://stackoverflow.com/questions/26623548
复制相似问题