update using max value of column

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

Transact-SQL (2000)

update using max value of column

Author

Topic

omicron777
Starting Member

9 Posts

Posted - 2010-09-30 : 13:02:20

I need help on how to update a column in a table getting the max value of a column that is on the same table.

Don't mind the dashes, it's for clarity only.

TABLE before update
CARD ---------- CATEGORY ------ INV_COUNT ----- CATEGORY_INV_COUNT
PRODUCT1 ---------- 1000 ---------- 0
PRODUCT2 ---------- 1001 ---------- 1
PRODUCT3 ---------- 1000 ---------- 0
PRODUCT4 ---------- 1000 ---------- 5
PRODUCT5 ---------- 1002 ---------- 0
PRODUCT6 ---------- 1001 ---------- 0
PRODUCT7 ---------- 1000 ---------- 2
PRODUCT8 ---------- 1000 ---------- 0
PRODUCT9 ---------- 1002 ---------- 0

TABLE after update (the result that I need)
CARD ---------- CATEGORY ------ INV_COUNT ----- CATEGORY_INV_COUNT
PRODUCT1 ---------- 1000 ---------- 0 ------------- 5
PRODUCT2 ---------- 1001 ---------- 1 ------------- 1
PRODUCT3 ---------- 1000 ---------- 0 ------------- 5
PRODUCT4 ---------- 1000 ---------- 5 ------------- 5
PRODUCT5 ---------- 1002 ---------- 0 ------------- 0
PRODUCT6 ---------- 1001 ---------- 0 ------------- 1
PRODUCT7 ---------- 1000 ---------- 2 ------------- 5
PRODUCT8 ---------- 1000 ---------- 0 ------------- 5
PRODUCT9 ---------- 1002 ---------- 0 ------------- 0

I am issuing this query:

update table set category_inv_count =
(select max(inv_count)
from table t1
left join table t2 on t1.category = t2.category
where t1.category = t2.category)

but I get this kind of result, which is incorrect.

CARD ---------- CATEGORY ------ INV_COUNT ----- CATEGORY_INV_COUNT
PRODUCT1 ---------- 1000 ---------- 0 ------------- 5
PRODUCT2 ---------- 1001 ---------- 1 ------------- 5
PRODUCT3 ---------- 1000 ---------- 0 ------------- 5
PRODUCT4 ---------- 1000 ---------- 5 ------------- 5
PRODUCT5 ---------- 1002 ---------- 0 ------------- 5
PRODUCT6 ---------- 1001 ---------- 0 ------------- 5
PRODUCT7 ---------- 1000 ---------- 2 ------------- 5
PRODUCT8 ---------- 1000 ---------- 0 ------------- 5
PRODUCT9 ---------- 1002 ---------- 0 ------------- 5

Please help me on how to derive on the correct result. Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-30 : 13:26:34

update t1
set category_inv_count = t2.category_inv_count
from table as t1
join (select CATEGORY, max(INV_COUNT) as category_inv_count from table group by CATEGORY) as t2
on t1.CATEGORY = t2.CATEGORY

No, you're never too old to Yak'n'Roll if you're too young to die.