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 |
Warsong
Starting Member
2 Posts |
Posted - 2009-05-19 : 09:11:20
|
Hey all! I'm having trouble creating a query. I'll try to explain it to you guys in the clearest way possible.
I have two tables:
ProductLocation which has (ProductID|LocationID)
CycleCount which has (Id|ProductID|TotalNumberOfLocations)
Currently, CycleCount.TotalNumberOfLocations is NULL for every record. My Goal is to Update that field with the total number of locations for all the corresponding products in a cyclecount.
Example if the tables are populated like this:
ProductLocation ---------------- Product A | Location X Product A | Location Y Product B | Location Z Product C | Location X Product C | Location Y
Total Number of Locations for A = 2, B = 1, C = 2
Cyclcount ---------------- ID 1 | Product A | NULL ID 1 | Product B | NULL ID 1 | Product C | NULL
I would need to change the NULL for Product A to 2, the NULL for b to 1 and the NULL for c to 2.
I tried doing some updates with count of productID from ProductLocation but SQL Server won't permit that. I hope you guys understand and can help me.
Thanks, Warsong
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-19 : 09:32:58
|
[code] UPDATE c SET TotalNumberOfLocations = p.cnt FROM Cyclecount c INNER JOIN ( SELECT ProductID, cnt = COUNT(*) FROM ProductLocation GROUP BY ProductID ) p ON c.ProductID = p.ProductID [/code]
KH [spoiler]Time is always against us[/spoiler] |
 |
|
Warsong
Starting Member
2 Posts |
Posted - 2009-05-19 : 10:16:35
|
Awesome! Works great! Thank you! =) |
 |
|
|
|
|