Update column | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Update column

hallo, help me pls. Maybe this subscribe is clear . I need to Update k_Table , column price Update k_Table set price= sum(Det.price)
FROM Det_v_lxxx Det , L_lxxxxx Lin
where cast(right(Det.dolNum,9) as bigint) >= Lin.dolNum AND cast(right(Det.volNum,9) as bigint) <= Lin.horNUm
group by lin.naks
Server: Msg 157, Level 15, State 1, Line 1
An aggregate may not appear in the set list of an UPDATE statement.
Thanks. Lubo
Use a correlated subquery to calculate the sum, and drop the GROUP BY clause. I assume you also need to add some criteria to the subquery to determine how the other two tables relate to the row in k_Table that you’re updating (those criteria would go into the subquery and make it a correlated subquery). UPDATE k_Table
SET price = (SELECT SUM(Det.price) from Det, Lin, k_Table WHERE …….)
Thanks , i modified some steps, but i have problem with writing statement
@sadZl , priceModif , price ARE decimal
Set @sqlCommand=’Update k_Table set priceModif= Select (price-(price/100)*’+ @sadZl+ ‘) from k_TAble) as varchar(255)’
Server: Msg 8115, Level 16, State 6, Line 85
Arithmetic overflow error converting varchar to data type numeric.

Thanks , i modified some steps, but i have problem with writing statement
@sadZl , priceModif , price ARE decimal
Set @sqlCommand=’Update k_Table set priceModif= Select (price-(price/100)*’+ @sadZl+ ‘) from k_TAble) as varchar(255)’
Server: Msg 8115, Level 16, State 6, Line 85
Arithmetic overflow error converting varchar to data type numeric.

Not sure that I understand how the "as varchar(255)" bit at the end would work in T-SQL … but your priceModif is certainly not expecting varchar data, so at the very least you should drop the "as varchar(255)" bit. Meanwhile, you should really start to look at what data you’re assembling, because you’re not really doing anything in aggregate like suggested by your original post. Look at the results when you run the query statement that determines the new value for the column: SELECT (price-(price/100)* 54) FROM k_Table I’ve inserted the value 54 for your @sadZl, just to give you an idea. This query returns the same number of rows as are present in k_Table, with the same value on each row.

]]>