I have a table that with the following query gives me all the double values.

SELECT workorder, job.count
FROM (
  SELECT workorder,
         COUNT(*) OVER (PARTITION BY workorder) AS count
  FROM engineering_job_schedule) AS job
WHERE job.count > 1 and workorder is not null

This will return something like

Now how would you use this query to create an UPDATE query to update all workorder values to a new counting-up number.

So that my new value would be

CodePudding user response：

You can use ROW_NUMBER() instead of COUNT(*) to calculate a number for every row. The ORDER BY clause is required so you'll have to find a column in engineering_job_schedule to specify the order.

Assuming the part you need to maintain is the first 16 characters, and the work order will be zero-padded to 6 digits, the query could look like this:

SELECT CONCAT( LEFT(workorder,16),
               ' ',
               REPLICATE('0', 6 - LEN(RN)),
               RN)
FROM 
(
    SELECT workorder,
        ROW_NUMBER() OVER (PARTITION BY workorder ORDER BY OrderDate) AS RN,
        COUNT(*) OVER (PARTITION BY workorder) AS count
    FROM engineering_job_schedule
) job
WHERE 
    job.count>1 and workorder is not null

Once you're satisfied with the output you can create an UPDATE query based on the results of the query:

UPDATE job
SET 
    workorder =CONCAT( LEFT(workorder,16),
               ' ',
               REPLICATE('0', 6 - LEN(RN)),
               RN)
FROM 
(
    SELECT workorder,
        ROW_NUMBER() OVER (PARTITION BY workorder ORDER BY OrderDate) AS RN,
        COUNT(*) OVER (PARTITION BY workorder) AS count
    FROM engineering_job_schedule
) job
WHERE 
    job.count>1 and workorder is not null