Le joyau caché du serveur SQL : UPDATE from SELECT
3 façons simples d’utiliser UPDATE from SELECT en vous facilitant la tâche
Vous avez probablement déjà été dans cette situation : Vous aviez besoin de mettre à jour les données d’une table en utilisant des informations stockées dans une autre table. Je rencontre souvent des personnes qui n’ont pas entendu parler de la puissante solution UPDATE from SELECT que SQL Server propose pour ce problème. En fait, j’ai lutté avec ce problème pendant un certain temps avant de découvrir ce joyau.
Dans les lignes qui suivent, je vais vous montrer trois astuces qui m’ont simplifié la vie à de nombreuses reprises. Pour cela, nous avons d’abord besoin de deux tables :
Si vous voulez suivre, vous pouvez obtenir le script ici : Le joyau caché de SQL Server – UPDATE from SELECT.sql
Développer votre UPDATE from a SELECT
Maintenant que nous avons mis en place l’environnement, plongeons dans la façon de faire fonctionner cette opération. Avant de vous montrer la solution multi-table, laissez-moi vous démontrer la forme la plus simple de la syntaxe UPDATE FROM et vous montrer une astuce simple pour rendre le développement de vos déclarations UPDATE vraiment simple, en écrivant d’abord une déclaration SELECT puis en la transformant en une mise à jour en supprimant deux caractères. Intrigué ?
La table dbo.Orders contient et la colonne is_archived. Celle-ci est utilisée pour archiver les commandes de plus de 90 jours. Cependant, nous avons la règle supplémentaire que les commandes qui n’ont pas encore été payées ou expédiées ne peuvent pas être archivées. (Dans cet exemple, la colonne is_archived n’a aucun effet physique. Consultez mon article SQL Server Pro Utilisation des partitions de table pour archiver les anciennes données dans les environnements OLTP si vous souhaitez savoir comment transformer la colonne is_archived en un véritable commutateur d’archivage.)
Une instruction SELECT pour renvoyer les enregistrements archivables ressemblerait à ceci :
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
ET O.is_shipped = 1;
Maintenant, ajoutons-y un peu de magie:
SELECT *, — */
is_archived = 1
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
Ceci est encore une simple instruction SELECT. Nous avons juste ajouté deux commentaires et une colonne supplémentaire. Cette colonne supplémentaire utilise la syntaxe alias peu commune nom = valeur avec est équivalente à la syntaxe plus commune valeur AS nom.
La beauté de la chose réside dans le fait que je peux transformer ce select en une instruction UPDATE syntaxiquement correcte, simplement en supprimant les deux tirets devant le mot-clé UPDATE :
SELECT *, — */
is_archived = 1
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
Ceci met à jour la table dbo.Orders comme le ferait une instruction UPDATE dbo.Orders SET…, car la table dbo.Orders est aliasée O et l’UPDATE fait référence à ce même alias O.
Ajout d’un JOIN à l’instruction UPDATE
La question qui nous a laissés ici était de savoir comment nous pouvons utiliser les données d’une table pour mettre à jour une autre table. Ne serait-ce pas bien si nous pouvions simplement » JOIN » ? Bonne nouvelle : La syntaxe ci-dessus nous permet de faire exactement cela.
Récemment, une colonne order_count a été ajoutée à la table dbo.Customers. Notre travail consiste maintenant à valoriser correctement cette colonne en fonction des commandes réelles passées par chaque client. Nous commençons à nouveau par écrire un select first:
SELECT *, — */
order_count = OA.cnt
FROM dbo.Customers AS C
JOIN(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUP BY O.customer_id
)OA
ON C.customer_id = OA.customer_id;
Une fois que l’instruction SELECT renvoie les bons résultats, il est facile de la faire passer en UPDATE:
SELECT *, — */
order_count = OA.cnt
FROM dbo.Customers AS C
JOIN(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUP BY O.customer_id
)OA
ON C.customer_id = OA.customer_id;
En raison de l’utilisation de l’alias C, SQL Server sait qu’il faut mettre à jour la table dbo.Customers tout en tirant les informations nécessaires des autres tables référencées dans l’instruction.
Utiliser UPDATE avec un CTE
Si les CTE sont votre truc, vous pouvez même aller un peu plus loin avec cela. Tant que SQL Server peut facilement déterminer ce que vous avez l’intention de mettre à jour, vous pouvez en fait » UPDATE » directement un CTE en utilisant une syntaxe très similaire :
(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUPE PAR O.customer_id
),
customer_order_counts AS
(
SELECT
C.customer_id,
C.name,
C.order_count,
OC.cnt new_order_cnt
FROM dbo.Customers AS C
JOIN order_counts AS OC
ON C.customer_id = OC.customer_id
)
UPDATE COC SET /*
SELECT *, — */
order_count = COC.new_order_cnt
FROM customer_order_counts AS COC;
La seule chose que vous ne pouvez pas faire avec l’une des déclarations ci-dessus, est de mettre à jour les données dans deux tables en même temps.
Un mot d’avertissement
Cette syntaxe nous fournit un moyen très puissant d’écrire des instructions UPDATE qui nécessitent des données de plus d’une table. Cependant, faites attention à ne pas écrire un code qui présente un comportement aléatoire. Jetez un œil à cette instruction UPDATE:
Tout comme le SELECT mis en évidence renvoie un client avec plusieurs commandes plusieurs fois, l’UPDATE mettrait joyeusement à jour chaque client plusieurs fois, en écrasant à chaque fois la modification précédente. Seule la dernière modification persisterait.
Le problème avec cela est qu’il n’y a aucune garantie dans quel ordre cela se produit. L’ordre dépend du plan d’exécution choisi et peut changer à tout moment. Ainsi, alors que l’instruction ci-dessus pourrait effectivement entraîner l’écriture de la dernière date de commande dans vos tests, elle s’exécutera probablement dans un ordre différent une fois que l’instruction rencontrera une grande quantité de données. Cela entraînera des problèmes difficiles à déboguer, alors faites attention à cette possibilité, lorsque vous rédigez vos déclarations UPDATE FROM SELECT.