Quiero actualizar una tabla con numeración consecutiva, comenzando con 1. La actualización tiene una cláusula where para que sólo los resultados que cumplen la cláusula será renumerado. Puedo lograr esto de manera eficiente sin necesidad de utilizar una tabla temporal?

  • Más probable es que esto depende de la versión específica de SQL que se ejecuta en, creo que no hay una manera estándar de hacer esto.
  • ¿Cuál sería el número se utiliza para? Se va a crear un ID? También, yo no me preocuparía por la ineficiencia. Dado que no hay otra información, no suena como usted estaría perdiendo algo por ser ineficiente. Suena como una sola operación.
  • Me recibe 1,5 millones de listados de 2 gb de tubería de archivos delimitados por cada semana. Necesito ejecutar un procedimiento almacenado que se da cuenta de que los listados son de mis clientes ciudades, darles el id de cliente y el número de ellos de forma secuencial para cada cliente. Debe ser eficiente.
  • Estoy utilizando MS SQL
  • Ahora que entiendo por qué estás numeración, La solución que he propuesto no hacer lo que usted necesita. Si varias actualizaciones se ejecutan al mismo tiempo, usted no conseguirá números consecutivos dentro de esa actualización. +1 a zombat y me han quitado mi respuesta.
InformationsquelleAutor Bryan | 2009-07-22

9 Comentarios

  1. 50

    Esto probablemente depende de su base de datos, pero aquí es una solución para MySQL 5 que implica el uso de una variable:

    SET @a:=0;
    UPDATE table SET field[email protected]a:[email protected]a+1 WHERE whatever='whatever' ORDER BY field2,field3

    Probablemente deberías editar tu pregunta e indicar la base de datos que está utilizando sin embargo.

    Edit: he encontrado una solución utilizando T-SQL de SQL Server. Es muy similar a la de MySQL método:

    DECLARE @myVar int
    SET @myVar = 0
    
    UPDATE
      myTable
    SET
      @myvar = myField = @myVar + 1
    • lo que sobre para MS SQL?
    • Gracias por la ayuda. Por lo que se incrementará myVar para cada registro de la actualización encuentra.. genial!!! ¿Qué pasa si mi numeración de campo es de tipo varchar en lugar de int. Puedo convertir myVar a varchar así? Gracias de nuevo a todos!
    • Impresionante…no sabía que se podía hacer eso. +1
    • Me gustaría pensar que usted podría utilizar varchars sin problemas… sin duda puede tener variables de cadena. Dependiendo de la forma en que SQL Server controla las matemáticas el uso de cadenas en lugar de enteros, usted puede ser hecho frente con un par de conversiones a lo largo de su instrucción SET, pero estoy segura de que no sería demasiado difícil.
    • El hombre, que funciona como un encanto. +1 para el loco maravilla.
    • Muy dulce, tenía la esperanza de que hay una manera de hacerlo.
    • Impresionante !! y funciona en MS SQL demasiado !!
    • MS SQL falta la ORDEN POR campo2, campo3
    • Nota, mediante la asignación de las operaciones en las consultas de SQL Server con las cláusulas ORDER BY tiene un comportamiento indefinido. connect.microsoft.com/SQLServer/Feedback/Details/383641 o marc.durdin.net/2015/07/… (esto nos habla acerca de las cadenas, pero con el mismo problema puede surgir)
    • Para cualquier otra persona que lea el código de SQL Server y era escéptico – lo que realmente hace la actualización de todas las filas que coincidan con la cláusula where
    • Para MS Sql debe agregar una sugerencia de consulta: OPCIÓN (MAXDOP 1) como se sugiere en stackoverflow.com/questions/1167885/…

  2. 28

    Para Microsoft SQL Server 2005/2008. Función ROW_NUMBER() se añadió en el año 2005.

    ; with T as (select ROW_NUMBER() over (order by ColumnToOrderBy) as RN
            , ColumnToHoldConsecutiveNumber from TableToUpdate
        where ...)
    update T
    set ColumnToHoldConsecutiveNumber = RN

    EDICIÓN: Para SQL Server 2000:

    declare @RN int
    set @RN = 0 
    
    Update T
    set ColumnToHoldConsecutiveNubmer = @RN
        , @RN = @RN + 1
    where ...

    NOTA: Cuando he probado el incremento de @RN apareció a suceder antes de la configuración de la columna de @RN, por lo que el de arriba te da los números a partir del 1.

    EDICIÓN: me di cuenta de que se parece usted desea crear varios números secuenciales dentro de la tabla. Dependiendo de los requisitos, usted puede ser capaz de hacer esto en un solo paso con SQL Server 2005/2008, mediante la adición de partition by a la over cláusula:

    ; with T as (select ROW_NUMBER() 
            over (partition by Client, City order by ColumnToOrderBy) as RN
         , ColumnToHoldConsecutiveNumber from TableToUpdate)
    update T
    set ColumnToHoldConsecutiveNumber = RN
    • Shannon su edición final, me ha funcionado varias veces, cuando necesito que el incremento de los números de reiniciar para cada grupo y subgrupo. Gracias. Su respuesta debe ser el puesto más alto.
  3. 2

    Así como el uso de una CTE o un CON, también es posible utilizar una actualización con un auto-unirse a la misma tabla:

    UPDATE a
    SET a.columnToBeSet = b.sequence
    FROM tableXxx a
    INNER JOIN
    (
       SELECT ROW_NUMBER() OVER ( ORDER BY columnX ) AS sequence, columnY, columnZ
       FROM tableXxx
       WHERE columnY = @groupId AND columnY = @lang2
    ) b ON b.columnY = a.columnY AND b.columnZ = a.columnZ

    La tabla derivada, alias «b», es utilizado para generar la secuencia a través de la función ROW_NUMBER() junto con algunas otras columnas que forman una virtual de la clave principal.
    Normalmente, cada fila se requiere una secuencia única de valor.

    La cláusula where es opcional y los límites de la actualización a las filas que cumplan las condiciones especificadas.

    La tabla derivada es, a continuación, se unió a la misma tabla, un alias, se unen en el virtual columnas de clave principal con el de la columna a ser actualizado en conjunto para generar la secuencia.

  4. 1

    Si desea crear un nuevo PrimaryKey columna, utilice esto:

    ALTER TABLE accounts ADD id INT IDENTITY(1,1) 
  5. 1

    He usado esta técnica durante años para rellenar los números ordinales y numerados secuencialmente columnas. Sin embargo, recientemente he descubierto un problema con él cuando se ejecuta en SQL Server 2012. Parece que internamente el motor de consulta es la aplicación de la actualización usando múltiples hilos y el predicado parte de la ACTUALIZACIÓN no está siendo manejado en una manera segura para subprocesos. Para hacer que funcione de nuevo tuve que volver a configurar SQL Server grado máximo de paralelismo de hasta 1 core.

    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO
    EXEC sp_configure 'max degree of parallelism', 1;
    GO
    RECONFIGURE WITH OVERRIDE;
    GO
    
    DECLARE  @id int
    SET @id = -1
    UPDATE dbo.mytable
    SET @id = Ordinal = @id + 1

    Sin esto, usted encontrará que la mayoría de los números secuenciales se duplican en toda la tabla.

    • Gracias por compartir esto. Es bueno saberlo!
    • Nota, mediante la asignación de las operaciones en las consultas de SQL Server con las cláusulas ORDER BY tiene un comportamiento indefinido. connect.microsoft.com/SQLServer/Feedback/Details/383641 o marc.durdin.net/2015/07/… (esto nos habla acerca de las cadenas, pero con el mismo problema puede surgir)
    • En lugar de cambiar la configuración del servidor, puede utilizar una sugerencia de consulta: OPCIÓN (MAXDOP 1)
  6. 1

    Para obtener el ejemplo por Shannon completamente de trabajo he tenido que editar su respuesta:

    ; WITH CTE AS (
        SELECT ROW_NUMBER() OVER (ORDER BY [NameOfField]) as RowNumber, t1.ID
        FROM [ActualTableName] t1
    )
    UPDATE [ActualTableName]
        SET Name = 'Depersonalised Name ' + CONVERT(varchar(255), RowNumber)
    FROM CTE
        WHERE CTE.Id = [ActualTableName].ID

    como su respuesta fue intentar actualizar T, que en su caso era el nombre de la Expresión de Tabla Común, y se produce un error.

  7. 0

    Unirse a los Números de la tabla? Se trata de una tabla extra, pero no sería temporal — te gustaría seguir los números de la tabla de alrededor como una utilidad.

    Ver http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

    o

    http://www.sqlservercentral.com/articles/Advanced+Consultar/2547/

    (esto último se requiere de un registro gratuito, pero me parece que es una muy buena fuente de consejos & técnicas para MS SQL Server, y mucho, es aplicable a cualquier implementación de SQL).

  8. -1

    Se es posible, pero sólo a través de algunos muy complicados de consultas – básicamente, usted necesita una subconsulta que cuenta el número de registros seleccionados hasta el momento, y lo usa como el IDENTIFICADOR de secuencia. Escribí algo parecido en algún momento funcionó, pero fue una gran cantidad de dolor.

    Para ser honesto, usted estaría mejor con una tabla temporal con un campo autoincrement.

Dejar respuesta

Please enter your comment!
Please enter your name here