I tried to word the question as best I could but I appreciate it might not be very clear.

I have the following table

I'm trying to run a query that will check if there are duplicates in original_ctr_id and if so, replace that number with the highest corresponding number in ctr_id.

In the example above, 898 occurs twice and the highest corresponding number in ctr_id is 899, so the table should look identical, however 898 does not appear at all and would be replaced in 1 row with 899.

This is just a small extract of the table as an illustration for what I'm trying to do. I've tried Partition by and Group by but I don't need ctr_id to be aggregated so that won't work as far as I know.

The query that is the closest to what I need is below, if I could somehow add some logic to select ctr_id instead of original_ctr_id when it's larger, this should give the desired result.

SELECT 
     original_ctr_id
FROM [MIS].[dbo].[Test]
GROUP BY 
    original_ctr_id
HAVING 
Count(original_ctr_id) >= 1 
ORDER BY original_ctr_id

Does anyone know if this is on the right track or have an elegant solution?

CodePudding user response：

At a guess, what you want here is an updatable CTE. This would work in SQL Server, but might not in a different product that uses T-SQL like SyBase:

WITH CTE AS(
    SELECT original_ctr_id,
           ctr_id,
           COUNT(*) OVER (PARTITION BY original_ctr_id) AS originals,
           MAX(ctr_id) OVER (PARTITION BY original_ctr_id) AS max_ctr_id
    FROM dbo.YourTable)
UPDATE CTE
SET ctr_id = max_ctr_id
WHERE ctr_id != max_ctr_id
  AND originals > 1