Procédure stockée pour mettre à jour les lignes s'il existe

J'ai une procédure stockée que je cours pour insert des valeurs si elles n'existent pas et mettre à jour si elles le font. J'ai une table d'où proviennent datatables

Table1 Name (nvarchar) Data (int) Timestamp (datetime)

Les données ressemblent à ceci

 Name1 5 2016-11-16 09:46:40.490 Name2 10 2016-11-16 09:48:35.240 Name1 7 2016-11-16 09:35:24.350 Name2 8 2016-11-15 02:27:44.670

J'essaie d'insert les noms dans une nouvelle table, moyenne les entiers, et groupe par jour. Voici la deuxième table

 Table2 Name (nvarchar) Data (int) Timestamp (date)

Les données ressemblent à ceci. Le nom1 était le même jour donc il a été moyenné. Name2 était des jours différents donc ils n'étaient pas moyennés.

 Name1 6 2016-11-16 00:00:00.000 Name2 10 2016-11-16 00:00:00.000 Name2 8 2016-11-15 00:00:00.000

J'ai converti l'horodatage à ce jour afin qu'il soit plus facile de combiner le jour. Ma procédure stockée ressemble à ceci

 IF NOT EXISTS (SELECT t.Name, t.Timestamp FROM Table2 t JOIN Table1 a ON t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp) GROUP BY t.Name, t.Timestamp) INSERT INTO Table2 (Name, Timestamp, Data) SELECT Name, CAST(Timestamp AS DATE) as Date, AVG(Data) as Average_Data FROM Table1 GROUP BY CAST(Timestamp AS DATE), Name ELSE UPDATE Table1 SET WHERE

Donc, la première fois que ça marche, il n'y a pas de problème. Les valeurs sont ajoutées et groupées correctement. Cependant, la deuxième fois qu'il fonctionne, il insère toujours.

Ma déclaration de mise à jour avait ressemblé à ceci avant que je l'efface

 SET Name = Name, Timestamp = Timestamp, Data = Date WHERE Name = Name, Timestamp = Timestamp

Je sais que ce n'est pas correct, mais il ne semble jamais même atteindre la mise à jour car l'insertion s'exécute toujours. Si je cours le Select dans le If Not Exists, je vois des données et il devrait le voir comme existe.

Je suppose que j'ai besoin d'aide pour nettoyer le If Not Exists et créer une instruction Update mise à jour.

MODIFIER:

Code mis à jour au dessous

 IF EXISTS(SELECT Name, Timestamp FROM Table2) UPDATE Table2 SET Name = a.Name, Timestamp = CONVERT(date, a.Timestamp), Data = AVG(a.Data) FROM Table2 t INNER JOIN Table1 a ON t.Name = a.Name WHERE t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp) ELSE INSERT INTO Table2 (Name, Timestamp, Data) SELECT Name, CAST(Timestamp AS DATE) as Date, AVG(Data) as Average_Data FROM Table1 GROUP BY CAST(Timestamp AS DATE), Name END

Personnellement, je préfère utiliser la technique de style plus ancien pour ce qui est communément connu comme un «upsert». MERGE fonctionne bien, mais il est très pénible de déboguer des problèmes car il fait tout en un coup.

Voici le type d'approche que je préfère, car il sépare l'insertion et la mise à jour, ce qui offre plus de flexibilité et facilite le debugging des problèmes. Vous pouvez également changer la jointure gauche dans l'insertion pour utiliser un NOT EXISTS avec une sous-requête corrélée, mais la plupart du time, la différence de performance est assez négligeable.

 UPDATE t SET Name = a.Name, Timestamp = CONVERT(date, a.Timestamp), Data = AVG(a.Data) FROM Table2 t INNER JOIN Table1 a ON t.Name = a.Name WHERE t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp) INSERT INTO Table2 (Name, Timestamp, Data) SELECT Name, CAST(Timestamp AS DATE) as Date, AVG(Data) as Average_Data FROM Table1 a left join Table2 t on a.Name = t.Name where t.Name is null GROUP BY Name, CAST(Timestamp AS DATE) , Name

— MODIFIER–

Je n'ai même pas remarqué l'agrégat dans votre mise à jour lorsque j'ai posté. Vous pouvez contourner cela facilement avec un cte.

 with cte as ( select Name = a.Name , Timestamp = CONVERT(date, a.Timestamp) , AverageData = AVG(a.Data) FROM Table2 t INNER JOIN Table1 a ON t.Name = a.Name WHERE t.Name = a.Name AND t.Timestamp = CONVERT(date, a.Timestamp) GROUP BY Name, CAST(Timestamp AS DATE) ) update t set Name = c.Name , Timestamp = c.Timestamp , Date = c.AverageData from Table2 t join cte c on c.Name = t.Name and c.Timestamp = t.Timestamp

J'aime utiliser l'instruction MERGE au lieu de UPDATE et INSERT consécutifs. En raison de l'agrégation, dans ma réponse ci-dessous, j'utilise un CTE avec le MERGE.

 -- CREATE AND INSERT TABLES DROP TABLE TABLE1 CREATE TABLE TABLE1 ( Name nvarchar(5) , Data int , Timestamp date ) INSERT INTO TABLE1 VALUES ('Name1',5 ,'2016-11-16') , ('Name2',10 ,'2016-11-16') , ('Name1',7 ,'2016-11-16') , ('Name2',8 ,'2016-11-15') , ('Name3',8 ,'2016-11-15') , ('Name3',10 ,'2016-11-15') , ('Name3',9 ,'2016-11-16') , ('Name3',11 ,'2016-11-16') DROP TABLE TABLE2 CREATE TABLE TABLE2 ( Name nvarchar(5) , Data int , Timestamp datetime ) INSERT INTO TABLE2 VALUES ('Name1',0 ,'2016-11-16') ,('Name2',0 ,'2016-11-16') ,('Name2',0 ,'2016-11-15') SELECT * FROM TABLE2 -- SHOW TABLE2 BEFORE MERGE BEGIN TRANSACTION -- HERE IS WHERE THE CODE THAT REPLACES YOUR QUERY ACTUALLY BEGINS ;WITH CTE_AVG_DATA_TABLE1 AS (SELECT Name , Timestamp , AVG(Data) AS [AVG(Data)] FROM TABLE1 GROUP BY Name , Timestamp ) MERGE Table2 AS TARGET USING CTE_AVG_DATA_TABLE1 AS SOURCE ON TARGET.Name = SOURCE.Name AND TARGET.TIMESTAMP = SOURCE.TIMESTAMP WHEN MATCHED THEN UPDATE SET TARGET.Name = SOURCE.Name , TARGET.Timestamp = CONVERT(date, SOURCE.Timestamp) , TARGET.Data = SOURCE.[AVG(Data)] WHEN NOT MATCHED THEN INSERT (Name, Timestamp, Data) VALUES (SOURCE.NAME, SOURCE.TIMESTAMP, SOURCE.[AVG(Data)]) ; SELECT * FROM TABLE2 -- SHOW TABLE2 AFTER MERGE ROLLBACK /*ALLOWS THIS TEST CODE TO BE RUN OVER AND OVER ADDING ROWS TO INSERT STATEMENTS ABOVE TO SHOW THAT IT WORKS */