Update avec MIN() conditionnel

**ctiguidou** · 27/06/2012, 15h17

Salut,
je désire mettre à jour une colonne dans Oracle à partir d'un calcul MIN() d'une date. Rien de compliqué mais je n'arrive pourtant pas à trouver la bonne syntaxe.
Pour faire simple, j'ai 2 tables T1 et T2 avec comme champs :
PO : Clé primaire
DT : Date

Je veux mettre à jour T1 avec le MIN (DT) de T2, uniquement si la date est différente.

Exemples :
T1 :
1 | 15/01/2012
2 | 30/03/2012
3 | 01/05/2012

T2 :
1 | 10/01/2012
1 | 15/01/2012
2 | 30/03/2012
2 | 14/04/2012

Résultats attendus dans T1 : 1 update de la première ligne
1 | 10/01/2012
2 | 30/03/2012
3 | 01/05/2012

J'ai fait ça comme requête mais je n'ai que des update (les 3 lignes) à NULL et je ne vois pas pourquoi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
UPDATE T1 set dt = 
   (
       SELECT 
         MIN_DT 
       FROM (SELECT PO, MIN (DT) AS MIN_DT
             from T2 RCL
             GROUP BY PO
            ) A
       WHERE T1.PO = A.PO
             AND T1.DT != A.MIN_DT
    );

Cette autre solution fonctionne et j'update avec la bonne valeur mais sur toutes les lignes même si la date est bonne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
UPDATE T1 set dt = 
   (
       SELECT 
         MIN(DT) AS MIN_DT
       FROM (SELECT PO, DT
             from T2
            ) A
       WHERE T1.PO = A.PO
    )
;

J'ai bien une autre solution qui fonctionne mais en ayant un WHERE sur l'update avec une sous requête dans laquelle je recalcule le MIN() donc c'est pas top et j'imagine qu'on peut faire plus optimisé.

Merci pour votre aide !

**Waldar** · 27/06/2012, 15h36

Quelle est votre version d'Oracle ?

**ctiguidou** · 27/06/2012, 15h38

Oracle 9 ou 10, ça dépend des environnements.

**Waldar** · 27/06/2012, 15h42

En 10g :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
MERGE INTO T1
USING ( SELECT PO, MIN (DT) AS MIN_DT
          FROM T2
      GROUP BY PO)  T2
   ON (T1.PO = T2.PO)
 WHEN MATCHED THEN UPDATE
  SET T1.DT =  T2.MIN_DT
WHERE T1.DT <> T2.MIN_DT;

**ctiguidou** · 27/06/2012, 15h56

Envoyé par Waldar

En 10g :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
MERGE INTO T1
USING ( SELECT PO, MIN (DT) AS MIN_DT
          FROM T2
      GROUP BY PO)  T2
   ON (T1.PO = T2.PO)
 WHEN MATCHED THEN UPDATE
  SET T1.DT =  T2.MIN_DT
WHERE T1.DT <> T2.MIN_DT;

J'obtiens un ORA-00905 Missing keyword. Une question de version d'Oracle ?

**Waldar** · 27/06/2012, 16h01

Probablement. Vous avez testé sur la 10g ?

**ctiguidou** · 27/06/2012, 16h12

Envoyé par Waldar

Probablement. Vous avez testé sur la 10g ?

Testé en 11 et ça fonctionne. Mais j'ai une DB en v9 donc ça va poser un souci cette syntaxe.

**Yanika_bzh** · 27/06/2012, 16h19

Avez tenté ce genre de chose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
UPDATE
  T1
SET DT= (SELECT Case WHEN T1.DT<> ST2.DT THEN T1.DT ELSE ST2.DT END
FROM
(SELECT T2.PO as PO , MIN(DT) as DT FROM T2
   GROUP BY T2.PO) ST2
WHERE T1.PO = ST2.PO)

A adapter et a tester, bien evidemment.

**Waldar** · 27/06/2012, 16h33

Envoyé par ctiguidou

Testé en 11 et ça fonctionne. Mais j'ai une DB en v9 donc ça va poser un souci cette syntaxe.

En 9i il faut ruser :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
MERGE INTO T1
USING ( SELECT PO, MIN (DT) AS MIN_DT
          FROM T2
         WHERE PO IN (SELECT PO FROM T1)
      GROUP BY PO)  T2
   ON (T1.PO = T2.PO)
 WHEN MATCHED THEN UPDATE
  SET T1.DT =  T2.MIN_DT
WHERE T1.DT <> T2.MIN_DT
 WHEN NOT MATCHED THEN INSERT (PO) VALUES (NULL);

Mais si vous aviez cherché vous l'auriez surement trouvé tout seul.

**ctiguidou** · 27/06/2012, 16h34

Envoyé par Yanika_bzh

Avez tenté ce genre de chose :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
UPDATE
  T1
SET DT= (SELECT Case WHEN T1.DT<> ST2.DT THEN T1.DT ELSE ST2.DT END
FROM
(SELECT T2.PO as PO , MIN(DT) as DT FROM T2
   GROUP BY T2.PO) ST2
WHERE T1.PO = ST2.PO)

A adapter et a tester, bien evidemment.

Oui ça fonctionne mais ça met à jour toute la table et non pas uniquement celles dont la date est différente du MIN().

**ctiguidou** · 27/06/2012, 16h48

Envoyé par Waldar

En 9i il faut ruser :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
MERGE INTO T1
USING ( SELECT PO, MIN (DT) AS MIN_DT
          FROM T2
         WHERE PO IN (SELECT PO FROM T1)
      GROUP BY PO)  T2
   ON (T1.PO = T2.PO)
 WHEN MATCHED THEN UPDATE
  SET T1.DT =  T2.MIN_DT
WHERE T1.DT <> T2.MIN_DT
 WHEN NOT MATCHED THEN INSERT (PO) VALUES (NULL);

Mais si vous aviez cherché vous l'auriez surement trouvé tout seul.

Ben j'ai cherché et ce que j'ai trouvé c'est que le MERGE conditionnel a été introduit avec Oracle 10, donc ce qui coince ici c'est l'avant dernière ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE T1.DT <> T2.MIN_DT

Si je l'enlève ça fonctionne mais du coup je perds la condition.

**mnitu** · 27/06/2012, 17h19

Déplacez le filtre du Where dans un Case

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
...
  Set T1.DT =  Case When T1.DT <> T2.MIN_DT Then T2.MIN_DT Else T1.DT End
...

**ctiguidou** · 27/06/2012, 17h27

Envoyé par mnitu

Déplacez le filtre du Where dans un Case

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
...
  Set T1.DT =  Case When T1.DT <> T2.MIN_DT Then T2.MIN_DT Else T1.DT End
...

Oui mais avec cette syntaxe j'aurai un update pour toutes les lignes quand même alors que je ne veux updater que celles avec une date différente. C'est une question de volumétrie, j'ai quelques centaines de lignes à mettre à jour parmi plusieurs millions et hors de question de faire un update massif donc.

**skuatamad** · 27/06/2012, 18h29

En modifiant la requête du USING pour ne sélectionner que les lignes que l'on souhaite modifier c'est possible :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SQL> select * from t1;
 
        PO DT
---------- --------
         1 15/01/12
         2 30/03/12
         3 01/05/12
 
SQL> select * from t2;
 
        PO DT
---------- --------
         1 10/01/12
         1 15/01/12
         2 30/03/12
         2 14/04/12
         4 14/04/12
 
SQL> SELECT PO, MIN (DT) AS MIN_DT
  2    FROM T2
  3   GROUP BY PO
  4  having min(dt) <> (select dt from t1 where t1.po = t2.po);
 
        PO MIN_DT
---------- --------
         1 10/01/12
 
SQL> MERGE INTO T1
  2  USING ( SELECT PO, MIN (DT) AS MIN_DT
  3            FROM T2
  4           GROUP BY PO
  5          having min(dt) <> (select dt from t1 where t1.po = t2.po)
  6        )  T
  7     ON (T1.PO = T.PO)
  8   WHEN MATCHED THEN UPDATE
  9    SET T1.DT =  T.MIN_DT
 10   WHEN NOT MATCHED THEN INSERT (PO) VALUES (NULL);
 
1 row merged.
 
SQL> select * from t1;
 
        PO DT
---------- --------
         1 10/01/12
         2 30/03/12
         3 01/05/12
 
SQL>

Mais est ce correct niveau perf ?

**ctiguidou** · 27/06/2012, 19h54

Envoyé par skuatamad

En modifiant la requête du USING pour ne sélectionner que les lignes que l'on souhaite modifier c'est possible :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SQL> select * from t1;
 
        PO DT
---------- --------
         1 15/01/12
         2 30/03/12
         3 01/05/12
 
SQL> select * from t2;
 
        PO DT
---------- --------
         1 10/01/12
         1 15/01/12
         2 30/03/12
         2 14/04/12
         4 14/04/12
 
SQL> SELECT PO, MIN (DT) AS MIN_DT
  2    FROM T2
  3   GROUP BY PO
  4  having min(dt) <> (select dt from t1 where t1.po = t2.po);
 
        PO MIN_DT
---------- --------
         1 10/01/12
 
SQL> MERGE INTO T1
  2  USING ( SELECT PO, MIN (DT) AS MIN_DT
  3            FROM T2
  4           GROUP BY PO
  5          having min(dt) <> (select dt from t1 where t1.po = t2.po)
  6        )  T
  7     ON (T1.PO = T.PO)
  8   WHEN MATCHED THEN UPDATE
  9    SET T1.DT =  T.MIN_DT
 10   WHEN NOT MATCHED THEN INSERT (PO) VALUES (NULL);
 
1 row merged.
 
SQL> select * from t1;
 
        PO DT
---------- --------
         1 10/01/12
         2 30/03/12
         3 01/05/12
 
SQL>

Mais est ce correct niveau perf ?

Merci je vais tester avec la clause HAVING.

**ctiguidou** · 27/06/2012, 20h23

Envoyé par skuatamad

En modifiant la requête du USING pour ne sélectionner que les lignes que l'on souhaite modifier c'est possible :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SQL> select * from t1;
 
        PO DT
---------- --------
         1 15/01/12
         2 30/03/12
         3 01/05/12
 
SQL> select * from t2;
 
        PO DT
---------- --------
         1 10/01/12
         1 15/01/12
         2 30/03/12
         2 14/04/12
         4 14/04/12
 
SQL> SELECT PO, MIN (DT) AS MIN_DT
  2    FROM T2
  3   GROUP BY PO
  4  having min(dt) <> (select dt from t1 where t1.po = t2.po);
 
        PO MIN_DT
---------- --------
         1 10/01/12
 
SQL> MERGE INTO T1
  2  USING ( SELECT PO, MIN (DT) AS MIN_DT
  3            FROM T2
  4           GROUP BY PO
  5          having min(dt) <> (select dt from t1 where t1.po = t2.po)
  6        )  T
  7     ON (T1.PO = T.PO)
  8   WHEN MATCHED THEN UPDATE
  9    SET T1.DT =  T.MIN_DT
 10   WHEN NOT MATCHED THEN INSERT (PO) VALUES (NULL);
 
1 row merged.
 
SQL> select * from t1;
 
        PO DT
---------- --------
         1 10/01/12
         2 30/03/12
         3 01/05/12
 
SQL>

Mais est ce correct niveau perf ?

Testé et approuvé, les perfs sont très bonnes

Par contre je n'ai pas le nombre de lignes mises à jour donc c'est troublant parce que je ne peux pas vérifier d'un coup d'oeil si l'update est correct. J'ai une requête de vérification mais un comptage après le merge serait pas mal comme dans ton exemple. J'utilise le logiciel PL/SQL Developer donc c'est peut être du à ça.
merci en tout cas.

**skuatamad** · 27/06/2012, 23h10

Je ne connais pas PL/SQL Developer, je pense qu'il peut afficher le nombre de lignes impactées mais je ne sais pas comment.
Un contournement serait d'utiliser un bloc PL/SQL et SQL%ROWCOUNT pour afficher le nombre de lignes merge, donc dans ton cas le nombre de lignes update :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> set serverout on
SQL> begin
MERGE INTO T1
USING ( SELECT PO, MIN (DT) AS MIN_DT
          FROM T2
         GROUP BY PO
        HAVING min(dt) <> (SELECT dt FROM t1 WHERE t1.po = t2.po)
      )  T
   ON (T1.PO = T.PO)
 WHEN MATCHED THEN UPDATE
  SET T1.DT =  T.MIN_DT
 WHEN NOT MATCHED THEN INSERT (PO) VALUES (NULL);
dbms_output.put_line('nb lignes merge = '||to_char(SQL%ROWCOUNT));
end;
/
nb lignes merge = 1
 
PL/SQL procedure successfully completed.
 
SQL>

**ctiguidou** · 28/06/2012, 15h43

Envoyé par skuatamad

Je ne connais pas PL/SQL Developer, je pense qu'il peut afficher le nombre de lignes impactées mais je ne sais pas comment.
Un contournement serait d'utiliser un bloc PL/SQL et SQL%ROWCOUNT pour afficher le nombre de lignes merge, donc dans ton cas le nombre de lignes update :

Merci de ton aide !

Update avec MIN() conditionnel

SQL Oracle

Discussions similaires

Partager

Partager