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 |
|
benking9987
Posting Yak Master
124 Posts |
 Posted - 2009-10-12 : 18:22:03
|
| Hi, I'm obviously new if I'm asking this question. i'm not familiar enough with SQL syntaxt to know how to do this next part of what I need.I have this statement:SELECT Item, Sum([QTY]*case when type = 'credit' then 1 else -1 end) AS CurrentOnhandFROM AZ_TransactionsGROUP BY AZ_Transactions.ItemORDER BY ItemThis sums up a table of individual transactions and gives me individual onhand inventory for all of my items. What I'd like to do now is take the results of this query and write them to another table. The table to which I am writing has a lot of different values among which is Item and CurrentOnhandQTY. So "Item" in the SS above is the relationship with the destination table.Any idea how to write this back?Alternatively, is there any way to set the value of a field on a table equal to the calculated total using something similar to the above? That way, all of my Onhand QTYs would be updated in real time.Thanks in advance. |
|
|
sanoj_av
Posting Yak Master
118 Posts |
Posted - 2009-10-12 : 23:36:56
|
| UPDATE <your_target_table_name> Set CurrentOnhandQTY=A.CurrentOnhandFROM ( SELECT Item, Sum([QTY]*case when type = 'credit' then 1 else -1 end) AS CurrentOnhand FROM AZ_Transactions GROUP BY AZ_Transactions.Item )AWHERE <your_target_table_name>.Item=A.Item |
 |
|
|
benking9987
Posting Yak Master
124 Posts |
Posted - 2009-10-13 : 11:41:12
|
| Fantastic! I think this worked. I'm going to validate the values, but I think you've got it! Thank you very much sanoj. |
|
|
|
|
|
|
SQL Syntax