Bonsoir NAGOL,
C’est parti pour un test !
Les tables :
CREATE TABLE SITE
(
id_site INT NOT NULL,
code_site VARCHAR(5) NOT NULL,
nom_site VARCHAR(48) NOT NULL,
CONSTRAINT SITE_PK PRIMARY KEY (ID_SITE),
CONSTRAINT SITE_AK UNIQUE (CODE_SITE)
)
;
CREATE TABLE TYPE_APPAREIL
(
type_appareil_id INT NOT NULL,
code_type_appareil VARCHAR(5) NOT NULL,
nom_type_appareil VARCHAR(48) NOT NULL,
CONSTRAINT TYPE_APPAREIL_PK PRIMARY KEY (type_appareil_id),
CONSTRAINT TYPE_APPAREIL_AK UNIQUE (code_type_appareil)
)
;
CREATE TABLE APPAREIL
(
id_appareil INT NOT NULL,
num_appareil VARCHAR(5) NOT NULL,
type_appareil_id INT NOT NULL,
CONSTRAINT APPAREIL_PK PRIMARY KEY (id_appareil),
CONSTRAINT APPAREIL_AK UNIQUE (type_appareil_id, num_appareil),
CONSTRAINT APPAREIL_TYPE_APPAREIL_FK FOREIGN KEY (type_appareil_id)
REFERENCES TYPE_APPAREIL
)
;
CREATE TABLE AFFECTATION_ACTUELLE
(
id_appareil INT NOT NULL,
id_site INT NOT NULL,
affectation_date_depuis DATE NOT NULL,
CONSTRAINT AFFECTATION_ACTUELLE_PK PRIMARY KEY (id_appareil),
CONSTRAINT AFFECTATION_ACTUELLE_APPAREIL_FK FOREIGN KEY (id_appareil)
REFERENCES APPAREIL ON DELETE CASCADE,
CONSTRAINT APPAREIL_SITE_FK FOREIGN KEY (id_site)
REFERENCES SITE,
)
;
CREATE TABLE AFFECTATION_HISTO
(
id_appareil INT NOT NULL,
affectation_date_debut DATE NOT NULL,
affectation_date_fin DATE NOT NULL,
id_site INT NOT NULL,
CONSTRAINT AFFECTATION_HISTO_PK PRIMARY KEY (id_appareil, affectation_date_debut),
CONSTRAINT AFFECTATION_HISTO_SITE_FK FOREIGN KEY (id_site)
REFERENCES SITE,
CONSTRAINT AFFECTATION_HISTO_APPAREIL_FK FOREIGN KEY (id_appareil)
REFERENCES APPAREIL ON DELETE CASCADE
)
;
INSERT INTO SITE (id_site, code_site, nom_site)
VALUES
(1, '001', 'site 001')
, (2, '002', 'site 002')
, (3, '003', 'site 003')
;
INSERT INTO TYPE_APPAREIL (type_appareil_id, code_type_appareil, nom_type_appareil) VALUES
(1, 'bn', 'bac à neige')
, (2, 'go', 'gouttière')
, (3, 'pv', 'pluviomètre')
;
INSERT INTO APPAREIL (id_appareil, num_appareil, type_appareil_id) VALUES
(11, '001', 1), (12, '002', 1), (13, '003', 1)
, (21, '001', 2), (22, '002', 2), (23, '003', 2)
, (31, '031', 1), (32, '032', 3), (33, '033', 1)
;
INSERT INTO AFFECTATION_ACTUELLE (id_appareil, id_site, affectation_date_depuis) VALUES
(11, 2, '2019-06-01')
, (21, 2, '2019-04-01')
, (31, 3, '2019-02-01')
;
INSERT INTO AFFECTATION_HISTO (id_appareil, affectation_date_debut, affectation_date_fin, id_site) VALUES
(11, '2010-02-01', '2010-11-30', 1)
, (11, '2010-12-01', '2015-03-31', 2)
, (11, '2015-04-01', '2017-10-31', 1)
, (11, '2017-11-01', '2019-05-31', 1)
, (21, '2012-02-01', '2012-11-30', 1)
, (21, '2012-12-01', '2014-03-31', 2)
, (21, '2014-04-01', '2018-10-31', 1)
, (21, '2018-11-01', '2019-03-31', 1)
, (31, '2007-02-01', '2012-11-30', 1)
, (31, '2012-12-01', '2014-03-31', 2)
, (31, '2014-04-01', '2016-10-31', 1)
, (31, '2016-11-01', '2019-01-31', 1)
;
La situation initiale est la suivante :
(1) AFFECTATION_ACTUELLE
id_appareil id_site affectation_date_depuis
----------- ------- -----------------------
11 2 2019-06-01
21 2 2019-04-01
31 3 2019-02-01
L’appareil 99 doit remplacer l’appareil 11.
Si l’appareil 99 n’existe pas déjà, on le crée :
INSERT INTO APPAREIL (id_appareil, num_appareil, type_appareil_id) VALUES
(99, '099', 1)
;
Méthode bourrin de remplacement :
UPDATE AFFECTATION_ACTUELLE
SET id_appareil = 99
WHERE id_appareil = 11
;
UPDATE AFFECTATION_ACTUELLE
SET id_appareil = 99
WHERE id_appareil = (SELECT id_appareil
FROM APPAREIL AS x
JOIN TYPE_APPAREIL AS y
ON x.type_appareil_id = y.type_appareil_id
WHERE nom_type_appareil = 'bac à neige'
AND num_appareil = '001')
;
Au résultat :
id_appareil id_site affectation_date_depuis
----------- ------- -----------------------
99 2 2019-06-01
21 2 2019-04-01
31 3 2019-02-01
(2) AFFECTATION_HISTO
Situation initiale, concernant l’appareil 11 à remplacer :
id_appareil affectation_date_debut affectation_date_fin id_site
----------- ---------------------- -------------------- -------
11 2010-02-01 2010-11-30 1
11 2010-12-01 2015-03-31 2
11 2015-04-01 2017-10-31 1
11 2017-11-01 2019-05-31 1
Méthode bourrin :
UPDATE AFFECTATION_HISTO
SET id_appareil = 99
WHERE id_appareil = 11
AND affectation_date_fin >= '2015-04-01'
;
Méthode pas bourrin :
UPDATE AFFECTATION_HISTO
SET id_appareil = 99
WHERE id_appareil = (SELECT id_appareil
FROM APPAREIL AS x
JOIN TYPE_APPAREIL AS y
ON x.type_appareil_id = y.type_appareil_id
WHERE nom_type_appareil = 'bac à neige'
AND num_appareil = '001')
AND affectation_date_fin >= '2015-04-01'
;
id_appareil affectation_date_debut affectation_date_fin id_site
----------- ---------------------- -------------------- -------
11 2010-02-01 2010-11-30 1
11 2010-12-01 2015-03-31 2
99 2015-04-01 2017-10-31 1
99 2017-11-01 2019-05-31 1
Les résultats sont-ils conformes à ce que vous attendez ?
Envoyé par NAGOL
.
Répondre avec citation
Partager