UPDATE d'un ID successeur

Version imprimable

Voir 40 message(s) de cette discussion en une page

15/07/2010, 17h20
chang_koukaii

UPDATE d'un ID successeur
bonjour à tous voici mon problème

j'ai la table suivante :
TOTO :
Code:

1 2 3 4 5 ID CODE DT_CREATION ID_SUCCESSEUR
Je dois mettre à jour l'ID_SUCCESSEUR, la règle est la suivante:
le successeur a le même CODE et sa DT_CREATION est le min des DT_CREATION supérieures à l'enregistrement courant (et dont l'ID est différent de l'ID courant).

Hormis passer par un curseur pour faire la recherche élément par élément je ne vois pas comment faire.

Je suis bloqué par l'interdiction d'utiliser un order by dans la sous requete de selection.

En gros, comment rendre correcte la requete suivante :
Code:

1 2 3 4 5 6 7 update TOTO a set a.ID_SUCCESSEUR = ( select c.id from (SELECT b.ID FROM TOTO b WHERE a.id <> b.id and a.code = b.code and a.dt_creation < b.dt_creation ) c where rownum = 1)

Essayez ainsi :

Code:

1
2
3
4
5
UPDATE TOTO a 
   SET a.ID_SUCCESSEUR = (SELECT MIN(b.id)
                            FROM TOTO b
                           WHERE a.code = b.code
                             AND a.dt_creation < b.dt_creation)

15/07/2010, 17h44
chang_koukaii

J'y ai pensé mais il se peut que id1 < id2 et que dt_creat1 > dt_creat2

sinon évidemment cela m'aurait simplifié la vie.

Ce n'était pas précisé dans la première règle de gestion.

S'il faut que l'id_successeur soit toujours supérieur, il suffit de rajouter le filtre :

Code:

1
2
3
4
5
6
UPDATE TOTO a 
   SET a.ID_SUCCESSEUR = (SELECT MIN(b.id)
                            FROM TOTO b
                           WHERE a.code = b.code
                             AND a.dt_creation < b.dt_creation
                             AND a.id < b.id);

15/07/2010, 18h17
chang_koukaii

Excuse moi je me suis mal exprimé
Il n y a pas de corrélations entre les id et les dates de création.
Je ne peux justement pas me baser sur l id pour définir in ordre
15/07/2010, 18h29
Waldar

Alors quel était le problème avec la première requête ?
N'hésitez pas à illustrer avec un exemple.
15/07/2010, 18h32
chang_koukaii

J aimerais trouver une alternative à la requete du premier post car elle n est pas valide cause du order by
15/07/2010, 19h09
rvfranck

Citation:

Envoyé par Waldar

Alors quel était le problème avec la première requête ?
N'hésitez pas à illustrer avec un exemple.

Oui, avec un exemple on comprendra mieux ce que tu veux avoir comme resultat.

Je veux faire ça :

Code:

1
2
3
4
5
6
UPDATE TOTO a 
SET a.ID_SUCCESSEUR = ( SELECT c.id FROM(SELECT b.ID FROM TOTO b
WHERE a.id <> b.id
  AND a.code = b.code
  AND a.dt_creation < b.dt_creation ) c
WHERE rownum = 1)

Mais valide

16/07/2010, 08h58
chang_koukaii

Pour résumer, la reqête juste au dessus ferait ce que je souhaite mettre à jour.
Cependant elle n'est pas valide à cause de la clause order by interdite dans un update.

Il me faut donc une requête valide avec le même traitement.
16/07/2010, 10h08
Waldar

Un exemple avec des données avant / après !

ok!!

j'ai les enregistrements suivants au départ :

Code:

1
2
3
4
5
6
7
 
ID    CODE    DT_CREATION   ID_SUCCESSEUR
1     code1    01/01/2001      null
2     code1    01/01/2002      null
4     code1    01/01/2003      null
3     code1    01/01/2004      null
5     code1    01/01/2005      null

je veux obtenir :

Code:

1
2
3
4
5
6
7
 
ID    CODE    DT_CREATION   ID_SUCCESSEUR
1     code1    01/01/2001      2
2     code1    01/01/2002      4
4     code1    01/01/2003      3
3     code1    01/01/2004      5
5     code1    01/01/2005      null

Ok c'est plus clair ainsi.

Le jeu de test :

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
create table toto
(
    ID            number(1),
    CODE          varchar2(5),
    DT_CREATION   date,
    ID_SUCCESSEUR number(1)
);
 
insert into toto (ID, CODE, DT_CREATION)
select 1, 'code1', to_date('01/01/2001', 'dd/mm/yyyy') from dual union all
select 2, 'code1', to_date('01/01/2002', 'dd/mm/yyyy') from dual union all
select 4, 'code1', to_date('01/01/2003', 'dd/mm/yyyy') from dual union all
select 3, 'code1', to_date('01/01/2004', 'dd/mm/yyyy') from dual union all
select 5, 'code1', to_date('01/01/2005', 'dd/mm/yyyy') from dual;
 
commit;
 
  select ID, CODE, DT_CREATION, ID_SUCCESSEUR
    from toto
order by DT_CREATION asc;
 
ID	CODE	DT_CREATION	ID_SUCCESSEUR
1	code1	01/01/2001	
2	code1	01/01/2002	
4	code1	01/01/2003	
3	code1	01/01/2004	
5	code1	01/01/2005

La requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
UPDATE TOTO a 
   SET a.ID_SUCCESSEUR = (SELECT c.ID
                           FROM TOTO c
                          WHERE c.dt_creation = (SELECT MIN(b.dt_creation)
                                                   FROM TOTO b
                                                  WHERE a.code = b.code
                                                    AND a.dt_creation < b.dt_creation));
 
  select ID, CODE, DT_CREATION, ID_SUCCESSEUR
    from toto
order by DT_CREATION asc;
 
ID	CODE	DT_CREATION	ID_SUCCESSEUR
1	code1	01/01/2001	2
2	code1	01/01/2002	4
4	code1	01/01/2003	3
3	code1	01/01/2004	5
5	code1	01/01/2005

Une autre méthode en utilisant MERGE et une fonction de fenêtrage :

Code:

1
2
3
4
5
6
 MERGE INTO TOTO a
 USING (select ID, LEAD(ID) over(partition by code order by dt_creation asc) as ID_SUCCESSEUR
         from toto) b
    ON (a.id = b.id)
  WHEN MATCHED THEN
UPDATE SET a.ID_SUCCESSEUR = b.ID_SUCCESSEUR;

Salut !

Et comme souvent avec ce genre de chose, on peut aussi court-circuiter un peu avec KEEP ;)

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
 
SQL> ed
Úcrit fichier afiedt.buf
 
  1  UPDATE TOTO a
  2     SET a.ID_SUCCESSEUR = (SELECT min(c.ID) keep(dense_rank first order by c.dt_creation)
  3                             FROM TOTO c
  4*                           WHERE a.dt_creation < c.dt_creation)
SQL> /
 
5 ligne(s) mise(s) Ó jour.
 
SQL>   SELECT ID, CODE, DT_CREATION, ID_SUCCESSEUR
  2      FROM toto
  3  ORDER BY DT_CREATION ASC;
 
        ID CODE  DT_CREAT ID_SUCCESSEUR
---------- ----- -------- -------------
         1 code1 01/01/01             2
         2 code1 01/01/02             4
         4 code1 01/01/03             3
         3 code1 01/01/04             5
         5 code1 01/01/05
 
SQL>

Salut,

Pourquoi est ce que ce code ne marche pas?

Code:

1
2
3
4
5
6
update toto a
set a.id_successeur = (
                      select lead(b.id) over(partition by code order by b.dt_creation) as val
                      from toto b
                      where b.id = a.id
                      );

Edit:
Alors que ca ca marche

Code:

1
2
3
4
5
6
7
8
update toto a
set a.id_successeur = (select val from (
                              select id,lead(b.id) over(partition by code order by b.dt_creation) as val
                              from toto b
                               )
 
                               where id = a.id
                      );

Ça ne retourne rien :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
UPDATE toto a
SET a.id_successeur = (SELECT lead(b.id) over(partition BY b.code ORDER BY b.dt_creation) AS val
                         FROM toto b
                        WHERE b.id = a.id);
 
-- 5 rows updated
 
  SELECT ID, CODE, DT_CREATION, ID_SUCCESSEUR
    FROM toto
ORDER BY DT_CREATION ASC;
 
ID	CODE	DT_CREATION	ID_SUCCESSEUR
1	code1	01/01/2001	
2	code1	01/01/2002	
4	code1	01/01/2003	
3	code1	01/01/2004	
5	code1	01/01/2005

20/07/2010, 10h35
boutade80

J'ai mis a jour mon message waldar.
La seconde requete fonctionne bien.
20/07/2010, 10h37
Waldar

Effectivement la seconde requête fonctionne bien, c'est une bonne solution !
20/07/2010, 10h40
boutade80

Et pourquoi la seconde marche et pas la premiere.
Elles me semblent tres similaire. c'est comme si ds la premiere fonction l'evaluation sur la condition a.id=b.id ne fonctionnait pas.
20/07/2010, 10h45
Waldar

Après la jointure entre a et b il ne reste plus qu'une seule ligne, donc la fonction LEAD va renvoyer null.

Voir 40 message(s) de cette discussion en une page