Update avec MIN() conditionnel

Version imprimable

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:

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:

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 !

27/06/2012, 15h36
Waldar

Quelle est votre version d'Oracle ?
27/06/2012, 15h38
ctiguidou

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

En 10g :

Code:

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;

Citation:

Envoyé par Waldar

En 10g :

Code:

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 ?

27/06/2012, 16h01
Waldar

Probablement. Vous avez testé sur la 10g ?
27/06/2012, 16h12
ctiguidou

Citation:

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.

Avez tenté ce genre de chose :

Code:

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.

Citation:

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:

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.

27/06/2012, 16h34
ctiguidou
Citation:
Envoyé par Yanika_bzh

Avez tenté ce genre de chose :

Code:

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().

Citation:
Envoyé par Waldar
En 9i il faut ruser :
Code:

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:

WHERE T1.DT <> T2.MIN_DT

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

Déplacez le filtre du Where dans un Case

Code:

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

27/06/2012, 17h27
ctiguidou
Citation:
Envoyé par mnitu

Déplacez le filtre du Where dans un Case

Code:

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.

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

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
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 ?

Citation:

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:

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.

Citation:

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:

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 :ccool:
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.

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:

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>

28/06/2012, 15h43
ctiguidou

Citation:

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 !