Nombre de mise à jour de la colonne à partir des données d'une autre table

Dans ma DB j'ai deux tables Articles(Id, ..., ToatlViews int) et ItemViews (id, ItemId, Timestamp)

Dans ItemViews tableau I de stocker tous les points de vue de l'un des éléments comme ils viennent sur le site. De temps à autre, je veux appeler une procédure stockée pour mettre à jour des Éléments.ToatlViews champ. J'ai essayé de faire cette SP à l'aide d'un curseur ... mais la mise à jour énoncé est faux. Pouvez-vous m'aider à le corriger? Puis-je le faire sans curseur?

CREATE PROCEDURE UpdateItemsViews
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @currentItemId int
    DECLARE @currentItemCursor CURSOR
    SET @currentItemCursor = CURSOR FOR SELECT Id FROM dbo.Items

    OPEN @currentItemCursor
    FETCH NEXT FROM @currentItemCursor INTO @currentItemId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        Update dbo.Items set TotalViews = count(*) 
              from dbo.ItemViews where ItemId=@currentItemId
        FETCH NEXT FROM @currentItemCursor INTO @currentItemId
    END   
END
GO
  • Je vous recommande de faire un effort pour ne pas utiliser des curseurs lors de l'écriture de SQL, car il y aura toujours un set basé sur la façon d'écrire ce que vous voulez sur la base de données. Bien sûr, il y aura toujours une exception à cette règle.
InformationsquelleAutor Radu D | 2012-05-22
  1. 21

    Vous pouvez utiliser une mise à JOUR directe déclaration

    update Items set TotalViews = 
     (select COUNT(id) from ItemViews where ItemViews.ItemId = Items.Id)

    Vous pouvez tester les performances pour les différentes façons de le faire, si c'est important.

    • +1 pour l'élégance. En parlant de performance, je me souviens avoir lu quelque part que count(1) au lieu de count(id) est mieux (pas que vous auriez probablement le remarquer). Depuis le champ id n'a pas besoin d'être sélectionné dans le cadre de la requête...
    • c'est une idée fausse. COUNT(1) est seulement de mieux que COUNT(id) si nous parlons de la précision, et c'est seulement plus précis (ou modifiée de quelque façon que ce soit) id est nullable. Si vous voyez ce indiqué quelque part (autre que votre mémoire), veuillez le signaler, car il devrait être corrigé ou clarifiés.
    • Juste un commentaire - je pense que je vais avoir un moment difficile de trouver à nouveau l'article.
    • Problème avec cela, c'est qu'il va mettre en TotalViews 0 pour tout ce qui ne marche pas correspondre à la sélectionner
    • vous dire où il n'y a pas de correspondance entre les Éléments et ItemViews? Si, dans ce cas TotalViews devrait être 0 ou la valeur null est une règle d'entreprise. Dans ce cas, l'OP veut 0: null, elle serait d'utiliser un autre énoncé.
    • oui, je suis simplement en pointant qu'à quelqu'un d'autre pour être au courant de. Dans le cas où vous ne voulez pas tous les non-correspondance des enregistrements mis à 0 ou null, vous devez utiliser un CTE posté par ci-dessous

    InformationsquelleAutor stuartd
  2. 8

    Vous pouvez utiliser update ... from au lieu d'un curseur:

    update  i
set     TotalViews = iv.cnt
from    dbo.Item i
join    (
        select  ItemId
        ,       count(*) as cnt
        from    dbo.ItemViews
        group by
                ItemId
        ) iv
on      i.Id = iv.ItemId
    InformationsquelleAutor Andomar
  3. 2
    ;WITH x AS 
(
  SELECT ItemID, c = COUNT(*) 
  FROM dbo.ItemViews
  GROUP BY ItemID
)
UPDATE i
SET TotalViews = x.c
FROM dbo.Items AS i
INNER JOIN x
ON x.ItemID = i.ItemID;

    Mais pourquoi voulez-vous stocker cette valeur, alors que vous pouvez toujours obtenir le nombre au moment de l'exécution? Vous allez avoir à exécuter cette instruction de mise à jour chaque fois que vous touchez le ItemViews table, sinon le comte stockées avec les Articles va incorrecte.

    Ce que vous pouvez envisager de le faire à la place est la création d'une vue indexée:

    CREATE VIEW dbo.ItemViewCount
WITH SCHEMABINDING
AS
    SELECT ItemID, ItemCount = COUNT_BIG(*)
      FROM dbo.ItemViews
      GROUP BY ItemID;
GO
CREATE UNIQUE CLUSTERED INDEX x ON dbo.ItemViewCount(ItemID);

    Maintenant, vous pouvez vous joindre à la vue de vos requêtes et de savoir que le compte est toujours à jour (sans avoir à payer de pénalité de numérisation pour le compte de chaque élément). La baisse de la vue indexée est que vous ne payez que le coût progressivement quand il y a des insertions, mises à jour et suppressions à la ItemViews table.

    InformationsquelleAutor Aaron Bertrand
  4. 0

    J'ai trouvé cette question /réponse, un an après qu'il a été écrit et répondu. la réponse était bien, mais que je recherchais quelque chose d'un peu plus automatique. J'ai fini par écrire un déclencheur pour recalculer automatiquement la colonne lorsqu'une ligne correspondante dans l'autre table a été inséré, supprimé ou mis à jour.

    Je pense que c'est une meilleure solution que l'exécution de quelque chose à la main pour faire le recalcul comme il n'y a pas possibilité d'oublier quelqu'un pour exécuter le code:

    CREATE TRIGGER [dbo].[TriggerItemTotalViews] 
   ON  [dbo].[ItemViews]
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
SET NOCOUNT ON;

UPDATE [Items] 
SET [TotalViews] = 
    (
    SELECT COUNT(id) 
    FROM [ItemViews] 
    WHERE [ItemViews].[ItemId] = [Items].[ItemId]
    )
WHERE [Items].[ItemId] IN
    (
    SELECT [ItemId] FROM [INSERTED] 
    UNION 
    SELECT [ItemId] FROM [DELETED]
    )
END
    InformationsquelleAutor Derek Tomes
  5. 0

    Même, mais différent:

    declare @productId int = 24;
declare @classificationTypeId int = 86;

update s
set CounterByProductAndClassificationType = row_num
from Samples s
join
(
    select row_number() over (order by (select Id)) row_num, Id
    from Samples
    where 
        ProductId = @productId and
        ClassificationTypeId = @classificationTypeId
) s_row on s.Id = s_row.Id
    InformationsquelleAutor Asaf