Upadate with count - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Upadate with count

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! =)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources