Mise à jour SQL avec numérotation consécutive

Je veux mettre à jour une table avec numérotation consécutive en commençant par 1. La mise à jour a une clause where de sorte que les résultats répondent à la clause seront renumérotés. Puis-je réaliser cela de manière efficace, sans l'aide d'une table temporaire?

  • Plus cette probabilité dépend de la version de SQL, vous êtes en cours d'exécution sur, je ne pense pas qu'il y est un moyen standard pour ce faire.
  • Quel serait le nombre il être utilisé? Êtes-vous créer un ID? Aussi, je ne serais pas s'inquiéter à propos de l'inefficacité. Étant donné aucune autre information, il ne sonne pas comme vous l'auriez-être à côté de quelque chose en étant d'une inefficacité flagrante. Sonne comme un arrêt de l'opération.
  • Je reçois de 1,5 million de références 2 go de tuyau de fichiers délimités par des chaque semaine. J'ai besoin d'exécuter une procédure stockée qui détermine les listes sont dans mes clients de villes, de leur donner l'id du client, et de les numéroter séquentiellement pour chaque client. Il doit être efficace.
  • Maintenant que je comprends pourquoi vous êtes la numérotation, La solution que j'ai proposé de ne pas faire ce que vous avez besoin. Si plusieurs mises à jour sont en cours d'exécution dans le même temps, vous ne serez pas obtenir des chiffres consécutifs à l'intérieur de cette mise à jour. +1 pour zombat et j'ai supprimé ma réponse.
InformationsquelleAutor Bryan | 2009-07-22
  1. 52

    Cela dépend probablement sur votre base de données, mais voici une solution pour MySQL 5, qui implique l'utilisation d'une variable:

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

    Vous devriez modifier votre question et indiquer la base de données que vous utilisez, cependant.

    Edit: j'ai trouvé un solution en utilisant des T-SQL pour SQL Server. Il est très similaire à la base de la méthode:

    DECLARE @myVar int
SET @myVar = 0

UPDATE
  myTable
SET
  @myvar = myField = @myVar + 1
    • Merci pour l'aide. Donc, ça va incrémenter myVar, pour chaque enregistrement, la mise à jour trouve.. super! Que faire si mon champ de numérotation est de type varchar à la place de int. Puis-je convertir des myVar varchar ainsi? Merci encore à tous!
    • Génial...jamais imaginé que l'on pouvait le faire. +1
    • Je pense que vous pourriez utiliser varchars sans problèmes... vous pouvez certainement avoir des variables de chaîne de caractères. Selon la façon dont SQL Server traite les mathématiques à l'aide de cordes au lieu de les entiers, vous pourriez être confronté à un couple de conversions tout au long de votre déclaration, mais je parie qu'il ne devrait pas être trop difficile.
    • Assez doux, j'espérais qu'il y est un moyen de le faire.
    • MS SQL est absent de la COMMANDE PAR champ2, champ3
    • Remarque, à l'aide des opérations d'affectation dans les requêtes SQL Server avec des clauses ORDER BY a un comportement indéterminé. connect.microsoft.com/SQLServer/Feedback/Details/383641 ou marc.durdin.net/2015/07/... (il parle de chaînes de caractères, mais même question peut se poser)
    • À quelqu'un d'autre qui a lu le code SQL Server et était sceptique - il vraiment mettre à jour toutes les lignes correspondant à la clause where
    • Pour MS Sql, vous devez ajouter un indicateur de requête: OPTION (MAXDOP 1) comme suggéré dans le stackoverflow.com/questions/1167885/...

    InformationsquelleAutor zombat
  2. 30

    Pour Microsoft SQL Server 2005/2008. Fonction ROW_NUMBER() a été ajouté en 2005.

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

    EDIT: Pour SQL Server 2000:

    declare @RN int
set @RN = 0 

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

    REMARQUE: Quand j'ai testé l'incrément de @RN semble se produire avant le réglage de la colonne de @RN, de sorte que le ci-dessus donne les numéros commençant à 1.

    EDIT: je viens de remarquer que les apparaît que vous souhaitez créer plusieurs numéros d'ordre à l'intérieur de la table. Selon les besoins, vous pouvez être en mesure de le faire en un seul passage avec SQL Server 2005/2008, en ajoutant partition by à la over clause:

    ; 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 votre montage final a fonctionné pour moi à plusieurs reprises quand j'ai besoin de l'augmentation des nombres de redémarrer pour chaque groupe et sous-groupe. Je vous remercie. Votre réponse devrait être de rang supérieur.
    InformationsquelleAutor Shannon Severance
  3. 2

    Dans oracle cela fonctionne:

    update myTable set rowColum = rownum
where something = something else

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm#i1006297

    InformationsquelleAutor Jens Schauder
  4. 2

    Ainsi que l'utilisation d'un CTE ou d'un AVEC, il est également possible d'utiliser une mise à jour avec une auto-jointure à la même table:

    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 table dérivée, alias b, est utilisé pour générer la séquence via la fonction ROW_NUMBER() avec quelques autres colonnes qui forment un virtuel de la clé primaire.
    En règle générale, chaque ligne nécessitera une unique valeur de la séquence.

    OÙ la clause est facultative et limites de la mise à jour de ces lignes qui répondent aux conditions spécifiées.

    La table dérivée est ensuite jointe à la même table, alias un, de rejoindre sur le virtuel colonnes de clé primaire avec la colonne à être mise à jour à la séquence produite.

    InformationsquelleAutor Kevin Swann
  5. 1

    Si vous souhaitez créer une nouvelle colonne PrimaryKey, utilisez juste cette:

    ALTER TABLE accounts ADD id INT IDENTITY(1,1)
    InformationsquelleAutor eliashdezr
  6. 1

    J'ai utilisé cette technique depuis des années pour remplir les ordinaux et numérotés dans l'ordre des colonnes. Cependant j'ai récemment découvert un problème avec elle lors de l'exécution de SQL Server 2012. Il semblerait que, en interne, le moteur de requête est l'application de la mise à jour de l'utilisation de plusieurs threads et le prédicat partie de la mise à JOUR n'est pas traitée dans un thread-safe manière. Pour le faire fonctionner de nouveau, j'ai dû reconfigurer SQL Server du degré maximum de parallélisme jusqu'à 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

    Sans cela, vous trouverez que la plupart des numéros d'ordre sont dupliqué à travers la table.

    • Merci pour ce partage. Bon à savoir!
    • Remarque, à l'aide des opérations d'affectation dans les requêtes SQL Server avec des clauses ORDER BY a un comportement indéterminé. connect.microsoft.com/SQLServer/Feedback/Details/383641 ou marc.durdin.net/2015/07/... (il parle de chaînes de caractères, mais même question peut se poser)
    • Au lieu de changer les paramètres du serveur, vous pouvez utiliser un indicateur de requête: OPTION (MAXDOP 1)
    InformationsquelleAutor user2412960
  7. 1

    Pour obtenir l'exemple par Shannon pleinement de travail, j'ai dû modifier sa réponse:

    ; 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

    que sa réponse a été d'essayer de mettre à jour T, qui dans son cas était le nom de l'Expression de Table Commune, et il renvoie une erreur.

    InformationsquelleAutor Colin Wiseman
  8. 0

    Joindre à une table de Nombres? Il s'agit d'une table supplémentaire, mais il ne serait pas temporaire -- vous auriez du garder les numéros de table autour d'un utilitaire.

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

    ou

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

    (ce dernier nécessite une inscription gratuite, mais je trouve que c'est une très bonne source de conseils & techniques pour MS SQL Server, et un lot est applicable à n'importe quel SQL de mise en œuvre).

    InformationsquelleAutor Val
  9. -1

    Il est possible, mais seulement par le biais de certains de très compliqué requêtes - fondamentalement, vous avez besoin d'une sous-requête qui compte le nombre d'enregistrements sélectionnés jusqu'à présent, et l'utilise comme l'ID de séquence. J'ai écrit quelque chose de similaire à un seul point, il a travaillé, mais il a beaucoup de douleur.

    Pour être honnête, vous seriez mieux avec une table temporaire avec un champ de type autoincrement.

    InformationsquelleAutor Daniel Roseman