CODEWITHSUNDEEP
sqlsql-server-2012
JD123456
JD123456

Reputation: 157

Update rows with nested queries

I need to update the rows with SG='All' by perform some recalculations on those rows (for example the sum of the rows with SG!='All' groupped by column G). I tried doing it in the following manner but I get an error: 

WITH UpdateData as
(Select * from Tbl 
where SG!='All')

update Tbl 
Set Val =(select SUM(ROUND(ud.Val,2)) group by ud.G)
from UpdateData ud
where Tbl.SG='All' and ud.G = Tbl.G

Msg 164, Level 15, State 1, Line 6 Each GROUP BY expression must contain at least one column that is not an outer reference.

And here is how the table looks like:

G   SG  I   Val
B   All All 142.215 
B   T1  123 10.401
B   T1  123 60.957
B   T2  220 70.857
D   All All 96.003
D   T3  666 80.158
D   T5  700 15.845

After the update, on the first row the value should be: select ROUND(10.401,2) + ROUND(60.957,2) + ROUND(70.857,2) = 10.4+60.96+70.86 =142.22 instead of 142.215 The real situation is more complex and I want to avoid recalculating the all rows, so I prefer doing an update rather than delete and readd them. Thanks a lot

Upvotes: 0

Views: 32

Answers (2)

JD123456
JD123456

Reputation: 157

Works like this. Thanks Mihai! WITH UpdateData as (Select * from Tbl where SG!='All')

update Tbl 
Set Val =(select SUM(ROUND(ud.Val,2))  
from UpdateData ud
where Tbl.SG='All' and ud.G = Tbl.G
group by ud.G)
where Tbl.SG='All'

Upvotes: 1

Sanjay Bhardwaj
Sanjay Bhardwaj

Reputation: 412

hope this will work,

update Tbl 
set Tbl.Val = = t.Value
from
    (select SUM(ROUND(ud.Val,2)) as Value, ud.G
    from UpdateData ud
    where ud.G = Tbl.G
    group by ud.G) as t
where Tbl.SG='All'

Upvotes: 0

Related Questions