February 10, 2011 at 7:44 am
Hi. I would like to update a value based its ranking.
My query to return the ranking is as follows:
SELECT p.ProductId,scr.SupplierSequence, DENSE_RANK () OVER (PARTITION BY p.ProductId ORDER BY scr.SupplierSequence ASC) AS dr
FROM SupplierCrossReferences AS scr
INNER JOIN Products AS p ON p.Product = scr.Product
UPDATE:
If dr value = 1 SET MY VALUE to 1
If dr value = 2 SET MY VALUE to 2
If dr value = 3 SET MY VALUE to 3 etc...
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 10, 2011 at 12:01 pm
Use your query inside a CTE and update the CTE value:
;WITH cte AS
(SELECT
p.ProductId,
scr.SupplierSequence,
DENSE_RANK () OVER (PARTITION BY p.ProductId ORDER BY scr.SupplierSequence ASC) AS dr,
scr.MyValue
FROM SupplierCrossReferences AS scr
INNER JOIN Products AS p ON p.Product = scr.Product
)
UPDATE cte
SET MyValue = cte.dr
Disclaimer: due to missing sample data the code is untested.
February 10, 2011 at 12:52 pm
Thanks Lutz, that was perfect.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply