Update query with order by

Question

I am trying to update one column based on another column short. I am using order by, but while using select top(10000) my incremental number going 4000 series but I need from 101, but initially I declare int 100

DECLARE @IncrementValue int
SET @IncrementValue = 100

UPDATE CabecDoc 
SET CDU_NumberBook = @IncrementValue,
    @IncrementValue = @IncrementValue + 1 
FROM
    (SELECT TOP(7000) *
     FROM CabecDoc
     WHERE data BETWEEN '2019-05-01' AND '2019-07-17'
       AND Entidade = 'VD4' 
       AND tipodoc = 'VD' AND CDU_SimbolBook = '*'
     ORDER BY NumDoc ASC) CabecDoc

I need update column from 101 to an incremental number through 7000 records.

score 1 · Answer 1 · answered Jul 18 '19 at 01:13

Here's what you need. No need to declare variables.

UPDATE
  CabecDoc
SET
  CDU_NumberBook = 100 + RowNum
FROM
  (
    Select
      top(7000) *,  
      ROW_NUMBER() OVER(
        ORDER BY
          NumDoc
      ) AS RowNum
    FROM
      CabecDoc
    WHERE
      data between '2019-05-01'
      and '2019-07-17'
      and Entidade = 'VD4'
      and tipodoc = 'VD'
      and CDU_SimbolBook = '*'
    ORDER BY
      RowNum ASC
  ) CabecDoc

Update query with order by

1 Answers1