Update Inner Join Limit 1

**hgushgus** · 13/03/2017, 09h51

Salut,

Je n'arrive pas à parvenir à mes fins pour une effectuer un update somme toute assez simple.
J'ai besoin de faire une jointure pour cette update, or il n'est pas possible d'utiliser l'option "LIMIT 1".
Du coup je ne sais pas comment m'y prendre car il y a plusieurs correspondances de possible, et je ne voudrais retenir que le premier résultat résultant de la condition de jointure, car sinon d'une part ça prend beaucoup de trop de temps, et d'une autre béh il y a plus de résultats que d'enregistrements de possible ... En plus clair :

J'ai 2 tables :

une contenant des codes nafs (entiers ou juste les 3/4 premiers chiffres) + leurs intitulés (métier / secteur d'activité)
une autre assez conséquante contenant des données d'entreprises, où figurent leurs codes NAF entier, mais pas les intitulés sus-nommés

Donc tout simplement je voudrais pour chaque enregistrement de la table 2 aller piocher le premier résultat dans la table 1 les intitulés avec la condition :

table2.code_naf like CONCAT(table1.codes_naf, '%')

Si je lance la requête suivante, ça mouline c'est interminable et j'ai trop de résultat, car plusieurs correspondances ... d'où la nécessité de n'en retenir qu'une seule :

Code :

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

1
2
3
4
update table2 AS table2 
inner join table1 as table1 
set table2.menu = table1.menu, table2.ss_menu = table1.ss_menu 
where table2.code_naf like CONCAT(table1.codes_naf, '%')

Comment faire ?

Merci de vos lumières !

**hgushgus** · 13/03/2017, 22h25

Finalement avant de me pencher sur la condition :

Code :

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

like CONCAT(table1.codes_naf, '%')

Je vais "updater" avec une condition =

Mais je me demande si il y a une différence entre ces 2 requêtes ?

Code :

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

1
2
3
4
5
update bdd 
inner join naf_menu 
on bdd.code_naf = naf_menu.codes_naf_multi 
set bdd.menu = naf_menu.menu, bdd.ss_menu = naf_menu.ss_menu 
where trim(bdd.menu)!="" and trim(bdd.ss_menu) !="";

et :

Code :

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

1
2
3
4
update bdd 
inner join naf_menu 
set bdd.menu = naf_menu.menu, bdd.ss_menu = naf_menu.ss_menu 
where bdd.code_naf = naf_menu.codes_naf_multi  and trim(bdd.menu)!="" and trim(bdd.ss_menu) !="";

?

Car j'ai l'impression en lisant la doc que la condition de jointure "on t1.x = t2.y" doit être unique, porter sur des ids uniques ?
( = qu'on ne recontre qu'une seule fois 'x' dans 't1' et qu'une seule fois 'y' dans 't2' )
Ou je me trompe complètement !?

...

Dans mon cas on trouve plusieurs fois les mêmes valeurs 'codes_naf' dans la table 'bdd' puisque c'est un listing de 1'300'000 contacts ...

Merci de m'éclairer !

**Artemus24** · 14/03/2017, 07h30

Salut hgushgus.

Un exemple aurait été plus parlant. Je vais essayer d'interpréter votre problème : vous avez deux tables.
Dans la première table, plusieurs lignes sont candidates à la mise à jour de la seconde table.
Dans le cas de la multiplicité, vous dévez sélectionner selon un critère ce que vous désirez récupérer.

Dans l'exemple ci-après, j'ai sélectionner deux critères :
1) le plus petit suffixe de la chaine de caractères à tester.
2) la première chaîne de caractères répondant à la sélection.

A l'inverse de vous, je n'ai pas sélectionné mes lignes en fonction de "colonne like 'blabla%'", mais à partir de la fonction "left(colonne, longueur)".
Voici les exemples :

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
( `id`    integer unsigned  not null auto_increment primary key,
  `code`  char(03)          not null,
  `val`   integer           not null,
  index `idx` (`code`)
 ) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into   `tabone` (`code`,`val`) values
  ('12c',10),('34j',12),('56n',14),('78p',16),('90v',18),
  ('12d',20),('34f',22),('56o',24),('78q',26),('90w',28),
  ('12e',30),('34g',32),('56k',34),('78r',36),('90x',38),
  ('12a',40),('34h',42),('56l',44),('78s',46),('90y',48),
  ('12b',50),('34i',52),('56m',54),('78t',56),('90u',58)
--------------
 
--------------
select * from `tabone`
--------------
 
+----+------+-----+
| id | code | val |
+----+------+-----+
|  1 | 12c  |  10 |
|  2 | 34j  |  12 |
|  3 | 56n  |  14 |
|  4 | 78p  |  16 |
|  5 | 90v  |  18 |
|  6 | 12d  |  20 |
|  7 | 34f  |  22 |
|  8 | 56o  |  24 |
|  9 | 78q  |  26 |
| 10 | 90w  |  28 |
| 11 | 12e  |  30 |
| 12 | 34g  |  32 |
| 13 | 56k  |  34 |
| 14 | 78r  |  36 |
| 15 | 90x  |  38 |
| 16 | 12a  |  40 |
| 17 | 34h  |  42 |
| 18 | 56l  |  44 |
| 19 | 78s  |  46 |
| 20 | 90y  |  48 |
| 21 | 12b  |  50 |
| 22 | 34i  |  52 |
| 23 | 56m  |  54 |
| 24 | 78t  |  56 |
| 25 | 90u  |  58 |
+----+------+-----+
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
 
--------------
CREATE TABLE `tabtwo`
( `id`         integer unsigned  not null auto_increment primary key,
  `matricule`  char(02)          not null,
  `mont`       integer           not null
 ) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into   `tabtwo` (`matricule`,`mont`) values
  ('12',0),('34',0),('56',0),('78',0),('90',0)
--------------
 
--------------
select * from `tabtwo`
--------------
 
+----+-----------+------+
| id | matricule | mont |
+----+-----------+------+
|  1 | 12        |    0 |
|  2 | 34        |    0 |
|  3 | 56        |    0 |
|  4 | 78        |    0 |
|  5 | 90        |    0 |
+----+-----------+------+
--------------
update      `tabtwo`  as t1
inner join  `tabone`  as t2
        on  t2.code = (select min(code) from `tabone` as t3 where left(t3.code,2) = t1.matricule)
       set  t1.mont = t2.val
--------------
 
--------------
select * from `tabtwo`
--------------
 
+----+-----------+------+
| id | matricule | mont |
+----+-----------+------+
|  1 | 12        |   40 |
|  2 | 34        |   22 |
|  3 | 56        |   34 |
|  4 | 78        |   16 |
|  5 | 90        |   58 |
+----+-----------+------+
--------------
update      `tabtwo`  as t1
inner join  (select left(code,2) as bis, any_value(val) as val from `tabone` group by bis) as t2
        on  t2.bis  = t1.matricule
       set  t1.mont = t2.val
--------------
 
--------------
select * from `tabtwo`
--------------
 
+----+-----------+------+
| id | matricule | mont |
+----+-----------+------+
|  1 | 12        |   10 |
|  2 | 34        |   12 |
|  3 | 56        |   14 |
|  4 | 78        |   16 |
|  5 | 90        |   18 |
+----+-----------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Dans les deux cas, il y aura toujours 1 et 1 seule ligne qui sera sélectionnée dans la table 1.

@+

**hgushgus** · 14/03/2017, 10h06

Bonjour,

Ok super ! merci de la réponse.

Je vais lancer ça, mais avant je vais mettre à jour les correspondances exactes entières (condition '=' et non pas 'xyz%').

J'en reviens à ma dernière question (je suis embêtant

), y'a-t-il une différence entre ces 2 requêtes ?

Code :

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

1
2
3
4
5
update bdd 
inner join naf_menu 
on bdd.code_naf = naf_menu.codes_naf_multi 
set bdd.menu = naf_menu.menu, bdd.ss_menu = naf_menu.ss_menu 
where trim(bdd.menu) = "" and trim(bdd.ss_menu) = "";

et :

Code :

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

1
2
3
4
update bdd 
inner join naf_menu 
set bdd.menu = naf_menu.menu, bdd.ss_menu = naf_menu.ss_menu 
where bdd.code_naf = naf_menu.codes_naf_multi  and trim(bdd.menu) = "" and trim(bdd.ss_menu) = "";

?

Car j'ai l'impression en lisant la doc que la condition de jointure "on t1.x = t2.y" doit être unique, porter sur des ids uniques ?
( = qu'on ne recontre qu'une seule fois 'x' dans 't1' et qu'une seule fois 'y' dans 't2' )
Ou je me trompe complètement !? ...

Dans mon cas on trouve plusieurs fois les mêmes valeurs 'codes_naf' dans la table 'bdd' puisque c'est un listing de 1'300'000 contacts ...

Merci de m'éclairer !

**Artemus24** · 15/03/2017, 06h01

Salut hgushgus.

Envoyé par hgushgus

Car j'ai l'impression en lisant la doc que la condition de jointure "on t1.x = t2.y" doit être unique

Sur une jointure, non, la condition n'a pas besoin d'être unique.
Heureusement, sinon nous ne pourrions pas récupérer tous les tuples répondant à la condition.
Je parle ici de la jointure dans le cas d'un select.

Envoyé par hgushgus

Ou je me trompe complètement !?

Inversement, le update ne va pas traiter en cascade la mise à jour de la colonne.
Il s'arrête à la première ligne rencontrée et validée par la condition.

Envoyé par hgushgus

y'a-t-il une différence entre ces 2 requêtes ?

Du point de vue du résultat final, non, il n'y a pas de différence. Mais question performance, oui.
La jointure se traite en premier, tandis que le where se traite après avoir récupéré les lignes de la jointure.

@+

**hgushgus** · 15/03/2017, 08h27

Bonjour.
Merci de la réponse !
Donc quelle serait la plus rapide ?
Avec la condition de jointure ?

**Artemus24** · 15/03/2017, 10h01

Salut hgushgus.

La requête la plus rapide est celle avec la jointure et non avec le where.

@+

**hgushgus** · 15/03/2017, 10h12

Ok !
Mais celle avec la jointure comporte aussi un where ... donc plus rapide malgrès tout ?

**Artemus24** · 16/03/2017, 06h39

Salut hgushgus.

Oui, elle est plus rapide car pour le where, le sous-ensemble de lignes à traiter est plus petit.

Quelle est la solution que vous avez retenue ?

@+

**hgushgus** · 16/03/2017, 09h43

Salut,

J'ai retenu celle avec la condition de jointure 'on', je m'en doutais que ce serait mieux.

Toutefois ayant 1'300'000 lignes dans la table à remplir, chaque requête prend dans les 2heures de temps voir +
(en local avec WAMP, 10Go de RAM c'est pas un super serveur distant !)

**Artemus24** · 17/03/2017, 11h33

Salut hgushgus.

Vous devez ajouter un index sur la condition de jointure afin d'améliorer les performances de votre requêtes.

@+

**hgushgus** · 17/03/2017, 12h12

Bonjour,

Oui comme un idiot je ne l'ai pas fait ...
Il faudrait :

Code :

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

ALTER TABLE `bdd` ADD INDEX(` code_naf `);

ou bien :

Code :

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

ALTER TABLE `bdd` ADD PRIMARY KEY(` code_naf `);

?

(tout en sachant que j'ai créé une colonne ID en AI index primary key, mais que je n'utilise pas)

**Artemus24** · 17/03/2017, 12h58

Salut hgushgus.

Une "primary key" sert surtout à rendre les lignes de vos tables uniques.
C'est en général le traditionnel "id integer unsigned not null auto_increment primary key,".
Mais si vous n'avez aucune utilité de cette colonne(je parle de cette forme-ci), vous pouvez la remplacer par une autre clef candidate, qui sera aussi unique et non nulle.

De ce fait, une "primary key" et un "unique index" sont similaire dans leur comportement.
C'est l'usage qui va déterminer l'un ou l'autre dans votre table.

Dans la table "naf_menu", vous créez un index (une "primary key" ou un "unique index") sur la colonne "code_naf".
Cette colonne sera donc nécessairement unique et non nulle !

@+

Update Inner Join Limit 1

Requêtes MySQL

Discussions similaires

Partager

Partager