Discussion :

[laurentSc]

Bonjour,

ma table possède 5 colonnes : la clé primaire auto-incrémentée, 2 autres colonnes et enfin 2 clés étrangères.
Le SGBD est MySQL.
La table :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `li_license` (
  `LI_ident` int unsigned NOT NULL AUTO_INCREMENT,
  `LI_activate_date` date NOT NULL,
  `LI_deactivate_date` date DEFAULT NULL,
  `AP_ident` int unsigned NOT NULL,
  `US_ident` int unsigned NOT NULL,
  PRIMARY KEY (`LI_ident`),
  KEY `AP_ident` (`AP_ident`),
  KEY `US_ident` (`US_ident`),
  CONSTRAINT `li_license_ibfk_1` FOREIGN KEY (`AP_ident`) REFERENCES `ap_application` (`AP_ident`),
  CONSTRAINT `li_license_ibfk_2` FOREIGN KEY (`US_ident`) REFERENCES `usl_user_license` (`US_ident`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

La requête actuelle d'insertion :

Code php :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
$sql = <<<sql
INSERT INTO li_license
SET 
    LI_activate_date =  STR_TO_DATE( {$ppp($activate_date)} , '%b %d,%Y'),
    LI_deactivate_date =  STR_TO_DATE( {$ppp($deactivate_date)} , '%b %d,%Y'),
    AP_ident={$ppp($ap_ident)},
    US_ident = {$ppp($us_ident)} 
ON DUPLICATE KEY UPDATE 
    LI_activate_date =  STR_TO_DATE( {$ppp($activate_date)} , '%b %d,%Y'),
    LI_deactivate_date =  STR_TO_DATE( {$ppp($deactivate_date)} , '%b %d,%Y'),
    AP_ident={$ppp($ap_ident)},
    US_ident = {$ppp($us_ident)}    
sql;

Tel que c'est fait aujourd'hui, si on a le même utilisateur et la même application (colonnes AP_ident et US_ident) et que seules les données correspondants aux colonnes LI_activate_date et LI_deactivate_date sont différentes, on a un nouvel enregistrement alors que je voudrais qu'un UPDATE soit effectué. Peut-on résoudre cela en ajoutant une contrainte SQL ?

[escartefigue]

Bonjour LaurentSc

Une simple contrainte unique sur la paire US_ident + AP_ident suffirait à éviter les doublons

[laurentSc]

Bonjour escartefigue,

OK avec cette contrainte UNIQUE, j'éviterai les doublons, mais les nouvelles données seront ignorées au lieu de faire un UPDATE. Je compte donc faire un contrôle puis éventuellement un UPDATE par code (PHP).

[escartefigue]

Non : avec INSERT ON DUPLICATE UPDATE, en cas de violation de la contrainte unique, c'est bien l'UPDATE qui sera effectué.

Cette instruction est le substitut du MERGE proposé par la norme SQL et adopté par les autres SGBD.

[laurentSc]

Bonsoir,

tous comptes faits, je dois distinguer l'INSERT et l'UPDATE, donc faire 2 requêtes SQL, car dans ces 2 cas, le traitement effectué ensuite diffère...

[Artemus24]

Salut à tous.

Dans votre requête, pour faire la distinction entre une insertion et une mise-à-jour, il faut un critère qui dans votre cas est la clef primaire.
Or cette clef primaire repose uniquement sur la colonne "LI_ident" qui est auto incrémenté.
Il n'y aura donc pas de mise-à-jour mais uniquement des insertions.

Une simple contrainte unique sur la paire US_ident + AP_ident suffirait à éviter les doublons

C'est la solution. Mais au lieu de faire un index unique, le mieux est de permuter cet index avec la clef primaire.

Tous comptes faits, je dois distinguer l'INSERT et l'UPDATE, donc faire 2 requêtes SQL, car dans ces 2 cas, le traitement effectué ensuite diffère...

Ce n'est pas la question d'origine que vous posez.
Vous désirez faire un update quand la clef primaire composée des deux clefs étrangères existe dans votre table.
La solution que je propose est celle d'Escartefigue :

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
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_cs`
--------------
 
--------------
DROP TABLE IF EXISTS `Test`
--------------
 
--------------
CREATE TABLE `Test`
( `LI_ident`            integer unsigned NOT NULL AUTO_INCREMENT,
  `LI_activate_date`    date             NOT NULL,
  `LI_deactivate_date`  date                 NULL DEFAULT NULL,
  `AP_ident`            integer unsigned NOT NULL,
  `US_ident`            integer unsigned NOT NULL,
  `flag`                char(01)             NULL DEFAULT NULL,
  PRIMARY KEY  (`AP_ident`,`US_ident`),
  UNIQUE INDEX (`LI_ident`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_cs`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `Test` (`AP_ident`,`US_ident`,`LI_activate_date`,`LI_deactivate_date`) values
  (101, 201, '2022-01-01', NULL),
  (102, 205, '2022-02-01', NULL)
--------------
 
--------------
select * from `Test`
--------------
 
+----------+------------------+--------------------+----------+----------+------+
| LI_ident | LI_activate_date | LI_deactivate_date | AP_ident | US_ident | flag |
+----------+------------------+--------------------+----------+----------+------+
|        1 | 2022-01-01       | NULL               |      101 |      201 | NULL |
|        2 | 2022-02-01       | NULL               |      102 |      205 | NULL |
+----------+------------------+--------------------+----------+----------+------+
--------------
DROP TABLE IF EXISTS `Temp`
--------------
 
--------------
CREATE TABLE `Temp`
( `LI_ident`            integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `LI_activate_date`    date             NOT NULL,
  `LI_deactivate_date`  date                 NULL DEFAULT NULL,
  `AP_ident`            integer unsigned NOT NULL,
  `US_ident`            integer unsigned NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_cs`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `Temp` (`AP_ident`,`US_ident`,`LI_activate_date`,`LI_deactivate_date`) values
  (101, 201, '2022-01-01', '2022-01-31'),
  (103, 202, '2022-03-01', NULL)
--------------
 
--------------
select * from `Temp`
--------------
 
+----------+------------------+--------------------+----------+----------+
| LI_ident | LI_activate_date | LI_deactivate_date | AP_ident | US_ident |
+----------+------------------+--------------------+----------+----------+
|        1 | 2022-01-01       | 2022-01-31         |      101 |      201 |
|        2 | 2022-03-01       | NULL               |      103 |      202 |
+----------+------------------+--------------------+----------+----------+
--------------
insert into `test` (`AP_ident`,`US_ident`,`LI_activate_date`,`LI_deactivate_date`,`flag`)
  select `AP_ident`,
         `US_ident`,
         `LI_activate_date`,
         `LI_deactivate_date`,
         'I' as `flag`
    from `temp`
on duplicate key update `LI_activate_date`   = values(`LI_activate_date`),
                        `LI_deactivate_date` = values(`LI_deactivate_date`),
                        `flag`               = 'M'
--------------
 
--------------
select * from `Test`
--------------
 
+----------+------------------+--------------------+----------+----------+------+
| LI_ident | LI_activate_date | LI_deactivate_date | AP_ident | US_ident | flag |
+----------+------------------+--------------------+----------+----------+------+
|        1 | 2022-01-01       | 2022-01-31         |      101 |      201 | M    |
|        2 | 2022-02-01       | NULL               |      102 |      205 | NULL |
|        3 | 2022-03-01       | NULL               |      103 |      202 | I    |
+----------+------------------+--------------------+----------+----------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Je mets à jour la table "Test" à partir d'un jeu d'essai contenu dans la tale "Temp".
Trois cas peuvent arriver :
1) aucune modification d'une ligne existante : FLAG = NULL.
2) nouvelle insertion : FLAG = 'I'.
3) mise-à-jour d'une ligne : FLAG = 'M'.

En espérant, LaurentSc, que cela correspond à ta demande.

Cordialement.
Artemus24.
@+