Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
harsh11
Starting Member
2 Posts |
 Posted - 2011-06-21 : 14:17:55
|
| Hi,There are two update queries below, how to get the same result in a single query. I Tried making one update query which is given below, but gives different result. Condition is values cannot be assigned in where clause like sr=2 / sr=3---------------------------------------------------------declare @t table ( sr int identity, id int, transid int, [date] date, recqty int, recrate decimal(18,6), reccost decimal(18,6), issqty int, issrate decimal(18,6), isscost as (issrate * issqty))insert @t values(1640933, 100545, '2011-05-01', 280, 78.44, 21963.2, 0, 0),(1641900, 101204, '2011-05-10', 0, 0,0,1,79),(1642774, 101912,'2011-05-28', 0, 0,0,1,79)select * from @tupdate aset a.issrate = (select isnull((sum(b.reccost) - sum(b.isscost)) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0) from @t as b where b.sr < a.sr) from @t a where a.sr =2update aset a.issrate = (select isnull((sum(b.reccost) - sum(b.isscost)) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0) from @t as b where b.sr < a.sr) from @t a where a.sr =3 select * from @t--------------------------------------------------------------Use one follwing query instead of two update queries above & see the result. whats wrong in the query?-----------------------------------------------------------------update aset a.issrate = (select isnull((sum(b.reccost) - sum(b.isscost)) / nullif(sum(b.RecQty) - sum(b.issqty), 0), 0) from @t as b where b.sr < a.sr) from @t a where issqty > 0------------------------------------------------------------------Two rows updated separately, calculates correct result. If you try to do the same in single query gives wrong result(issrate of 3rd row). Please advice... |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-21 : 14:54:51
|
| In fact, the second query is correct, and so is the first. You can see this if you remove the update statement that has the "where a.sr=2" and run only the update statement that has "where a.sr=3".This happens because in the "where a.sr=2" you are updating isscost. That updated numbers are used in the "where a.sr=3" query.When you do it all in one shot, it is a SET OPERATION - which means, it does not go one row at a time. It is as though, there is an initial state and values in the row, and those values are used to calculate the result for EVERY row all in ONE-SHOT. |
 |
|
|
harsh11
Starting Member
2 Posts |
Posted - 2011-06-21 : 16:02:50
|
| I understood the problem from your explanation. But how to resolve this issue? How can I update all the rows in one-shot with the accurate values? How will it work if I update them in a loop? This example is for one product with minimum transactions. for 50 products with various transactions for each product it is going to be a time consuming query. |
|
|
|
|
|
|
|
|
Update Query