Update sur Ms Server avec [ With et CROSS]

Version imprimable

Bonjour

Problème : Mise à jour d'un champ de table à partir le résultat d'une requête faite par [ With et CROSS]

Je possède une table Checks

Code:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE dbo.Checks ( Client VARCHAR(32), RowNr int, Amount DECIMAL(12,2), Cumul DECIMAL(12,2), );
 
INSERT dbo.Checks(Client, RowNr, Amount)
          SELECT 'Company A', '1', 1
UNION ALL SELECT 'Company A', '2', 1
UNION ALL SELECT 'Company A', '3', 5
UNION ALL SELECT 'Company B', '4', 3
UNION ALL SELECT 'Company A', '5', 1
UNION ALL SELECT 'Company B', '6', 2
UNION ALL SELECT 'Company B', '7', 1
UNION ALL SELECT 'Company B', '8', 5;

Je dois calculer le cumul de la colonne Amount c'est pourquoi j'ai procédé à ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH Islands AS
(
  SELECT Client, RowNr, Amount, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId
  FROM Checks
)
 
SELECT *
FROM Islands I1
CROSS APPLY
(
  SELECT SUM(Amount) Cumul
  FROM Islands I2
  WHERE I1.Client = I2.Client
  AND I1.IslId = I2.IslId
  AND I1.RowNr >= I2.RowNr
)C

La requête dessus me calcul très bien le "Cumul" du champ "Amount"

Maintenant je veux bien mettre à jour le champ Cumul par la jointure checks.RowNr = Islands.RowNr mais je n'arrive pas à faire..! Qu'est ce qu'il y a comme problème sur le code dessous ?

Merci 1000 fois

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Update checks set checks.cumul = Islands.cumul from
WITH Islands AS
(
  SELECT Client, RowNr, Amount, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId
  FROM Checks
)
 
SELECT *
FROM Islands I1
CROSS APPLY
(
  SELECT SUM(Amount) Cumul
  FROM Islands I2
  WHERE I1.Client = I2.Client
  AND I1.IslId = I2.IslId
  AND I1.RowNr >= I2.RowNr
)C
 
join checks on
checks.RowNr  = Islands.RowNr

Peut-être comme cela ? (je n'ai pas les moyens de tester)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH    Islands 
AS  (   SELECT  Client
            ,   RowNr
            ,   Amount
            ,   RowNr - ROW_NUMBER() OVER(PARTITION BY Client ORDER BY RowNr) IslId
        FROM    Checks
    )
    ,  Cumul
AS  (   SELECT  *    
        FROM    Islands I1
        CROSS APPLY
            (   SELECT  SUM(Amount) Cumul
                FROM    Islands I2
                WHERE   I1.Client = I2.Client
                    AND I1.IslId = I2.IslId
                    AND I1.RowNr >= I2.RowNr
            )   C
    )
UPDATE  checks 
SET     checks.cumul = Cumul.cumul 
FROM    checks
    JOIN 
        Cumul
        ON  checks.RowNr = Islands.RowNr

Bonjour,

La clause WITH marquant la CTE doit obligatoirement débuter la requête.

Dans votre cas, puisque vous avez une requete qui fonctionne, vous pouvez la mettre également en CTE, et mettre à jour celle-ci. Il suffit pour cela d'ajouter la colonne cumul à votre CTE (car elle est la cible de la mise à jour)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 WITH Islands AS ( SELECT Client, RowNr, Amount,cumul, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId FROM Checks ), tmp as ( SELECT I1.cumul as C1, C.cumul as C2 FROM Islands I1 CROSS APPLY ( SELECT SUM(Amount) Cumul FROM Islands I2 WHERE I1.Client = I2.Client AND I1.IslId = I2.IslId AND I1.RowNr >= I2.RowNr )C ) update tmp set C1 = C2
Sans passer par une deuxième CTE (donc tel que vous essayiez de le faire), ce serait comme ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 WITH Islands AS ( SELECT Client, RowNr, Amount,cumul, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId FROM Checks ) UPDATE I1 SET cumul = C.cumul FROM Islands I1 CROSS APPLY ( SELECT SUM(Amount) Cumul FROM Islands I2 WHERE I1.Client = I2.Client AND I1.IslId = I2.IslId AND I1.RowNr >= I2.RowNr )C ;
Attention toutefois au CROSS APPLY qui peut être couteux sur des fortes volumétries.
Vous pouvez avantagement le remplacement par une jointure sur une sous requete en table dérivée :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 WITH Islands AS ( SELECT Client, RowNr, Amount,cumul, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId FROM Checks ) UPDATE I1 set Cumul = I2.cumul FROM Islands I1 INNER JOIN ( SELECT Client, IslId,RowNr ,SUM(Amount) Cumul FROM Islands GROUP BY Client, IslId,RowNr )I2 ON I1.Client = I2.Client AND I1.IslId = I2.IslId AND I1.RowNr >= I2.RowNr

Bonjour,

Je ne sais pas quelle version de SQL Serveur tu as. Cependant, il est possible depuis 2012 de faire des CTE sur des VALUES. Je m'explique.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
WITH ListInsert AS (
SELECT *
      , RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId
  FROM ( VALUES ( 'Company A', '1', 1 )
              , ( 'Company A', '2', 1 )
              , ( 'Company A', '3', 5 )
              , ( 'Company B', '4', 3 )
              , ( 'Company A', '5', 1 )
              , ( 'Company B', '6', 2 )
              , ( 'Company B', '7', 1 )
              , ( 'Company B', '8', 5 ) ) AS List (Client, RowNr, Amount) )
 
SELECT Client, RowNr, Amount, I1.IslId
 FROM ListInsert I1
 CROSS APPLY
(
  SELECT SUM(Amount) Cumul
  FROM ListInsert I2
  WHERE I1.Client = I2.Client
  AND I1.IslId = I2.IslId
  AND I1.RowNr >= I2.RowNr
)C

Je suis peut-être HS, mais je trouve cette forme plus élégante et surtout elle limite le nombre d'actions sur la table (un seul insert, pas d'update). Je conçoit que sur un si petit volume ce ne soit pas important, mais je suis maniaque :aie:

Cordialement,