How to update table with max function and flag should be marked as 'M' only one row.
asked
How to update table in below with max function
It is updating 2 two rows but i want only one row should be update.
UPDATE t
SET flag = 'm'
FROM test t
INNER JOIN (
SELECT companyid, MAX(shipment_count) as shipment_count
FROM test
GROUP BY companyid
) m
ON t.shipment_count = m.shipment_count
You will need to use row_number and a CTE to do this
with cte as ( select row_number() over (order by shipment_count desc) as rn, * from test ) update cte set flag = 'm' where rn = 1
15 People are following this question.
