Insert a consecutive number in a field sorted by another field in sql 2012

Question

Insert a consecutive number in a field sorted by another field in sql 2012

Navigation

#1 by (0 votes)

-1

Good afternoon, colleague, have the next case to see if they help me solve it, it turns out that I am working in sql server 2012 .

I want you to insert a consecutive number in a field x according to a certain order example:

This is my code

DECLARE @id AS INT    
SELECT  @id =ROW_NUMBER() OVER(ORDER BY a.COD_ART) , a.COD_ART FROM ARTFISIC AS a GROUP BY A.COD_ART ORDER BY a.COD_ART asc

UPDATE ARTFISIC
SET
MARCA_MARB='X',
    MARBETE=@id,
    CONTEO01='0',
     CONTEO02='0',
      CONTEO03='0',
      CONTEOCERO='0',
      DIFERENCIA='0',
      EXIST_CONT='0',
      ENT_RETRO='0',
      SAL_RETRO='0',
    EXIST_FISI='0',
    FIS_VS_TEO='0'

I get the following error:

A SELECT statement that assigns a value to a variable should not combine with data recovery operations.

I hope your usual help in advance I thank you

God bless you

sql sql-server

asked by Jose 22.11.2018 в 23:03

source

1 answer

Give access to a table in a database in SQL-SERVER My loop only works once, then it does not run again, and I have no idea why, it should run until the condition is n

score 0 · Answer 1

For what, you have to create a temporary table with the data sorted first, to be able to number them.

Select ROW_NUMBER() OVER(ORDER BY a.COD_ART ASC) as Numero, a.COD_ART
into #TablaTemporal
FROM ARTFISIC AS a

This table will give you a number in front of each article

Then, with this table, we can make an update of the other table, matching the field Marbete a Numero

UPDATE ARTFISIC set a.Marbete = b.numero
From ARTFISIC as a inner join 
#TablaTemporal as b on a.COD_ART = b.COD_ART

Remember to drop the temporary table once you have finished (although it should disappear once you log out).