How to write an update query that updated all double values in table with autonumbering based on count

nokiko 0
Viewed 30

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

workorder cnt
M-22.20.171.3017 000001 2
M-22.20.171.3017 000001 2
M-22.20.176.3023 000001 2
M-22.20.176.3023 000001 2

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

workorder
M-22.20.171.3017 000001
M-22.20.171.3017 000002
M-22.20.176.3023 000001
M-22.20.176.3023 000002
sql-server sql
1 Answers

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
Panagiotis Kanavos0
Related