MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `giorni` (
  `IDGiorno` int(11) NOT NULL,
  `DayofWeek` int(1) NOT NULL,
  `Nome` varchar(255) NOT NULL,
  `Aperto` tinyint(2) NOT NULL DEFAULT 1,
  `OrarioApertura` time DEFAULT NULL,
  `OrarioChiusura` time DEFAULT NULL,
  `FKRoom` int(11) NOT NULL,
  `FKApertura` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `giorni` (`IDGiorno`, `DayofWeek`, `Nome`, `Aperto`, `OrarioApertura`, `OrarioChiusura`, `FKRoom`, `FKApertura`) VALUES
(1, 2, 'Lun', 0, '09:00:00', '19:00:00', 1, 1),
(2, 3, 'Mar', 0, '09:00:00', '19:00:00', 1, 1),
(3, 4, 'Mer', 0, '09:00:00', '19:00:00', 1, 1),
(4, 5, 'Gio', 0, '09:00:00', '19:00:00', 1, 1),
(5, 6, 'Ven', 0, '09:00:00', '19:00:00', 1, 1),
(6, 7, 'Sab', 0, '09:00:00', '19:00:00', 1, 1),
(7, 1, 'Sun', 0, '09:00:00', '19:00:00', 1, 1);


select * from giorni


IDGiorno
DayofWeek
Nome
Aperto
OrarioApertura
OrarioChiusura
FKRoom
FKApertura


1
2
Lun
0
09:00:00
19:00:00
1
1

2
3
Mar
0
09:00:00
19:00:00
1
1

3
4
Mer
0
09:00:00
19:00:00
1
1

4
5
Gio
0
09:00:00
19:00:00
1
1

5
6
Ven
0
09:00:00
19:00:00
1
1

6
7
Sab
0
09:00:00
19:00:00
1
1

7
1
Sun
0
09:00:00
19:00:00
1
1


CREATE TABLE `room` (
  `IDRoom` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `Nomeroom` varchar(255) NOT NULL,
  `TipoSportroom` varchar(2) NOT NULL,
  `Categoria` varchar(100) DEFAULT NULL,
  `OrarioAperturaroom` time DEFAULT NULL,
  `OrarioChiusuraroom` time DEFAULT NULL,
  `Coperto` tinyint(1) DEFAULT NULL,
  `MaxPlayers` int(11) NOT NULL,
  `StatoBloccato` tinyint(1) DEFAULT NULL,
  `colore` varchar(50) NOT NULL DEFAULT 'blue',
  `Rank` int(100) NOT NULL DEFAULT 0,
  `Urlfoto` varchar(255) NOT NULL DEFAULT 'logo.jpg',
  `BreveDescrizione` varchar(255) NOT NULL DEFAULT 'Lorem ipsum dolor sit amet, consectetur adipisci elLorem ipsum dolor sit amet, consectetur adipisci',
  `NeedPrepaid` tinyint(1) NOT NULL DEFAULT 0,
  `DelayPreno` int(3) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dump dei dati per la tabella `room`
--

INSERT INTO `room` (`IDRoom`, `Nomeroom`, `TipoSportroom`, `Categoria`, `OrarioAperturaroom`, `OrarioChiusuraroom`, `Coperto`, `MaxPlayers`, `StatoBloccato`, `colore`, `Rank`, `Urlfoto`, `BreveDescrizione`, `NeedPrepaid`, `DelayPreno`) VALUES
(1, 'Tennis 1', 'TE', 'TEER', '09:00:00', '21:00:00', 0, 0, 0, 'blue', 0, 'logo.jpg', 'Lorem ipsum dolor sit amet, consectetur adipisci elLorem ipsum dolor sit amet, consectetur adipisci', 0, 0),
(2, 'Padel 1', 'PA', NULL, '09:00:00', '21:00:00', 0, 0, 0, 'red', 0, 'logo.jpg', 'Lorem ipsum dolor sit amet, consectetur adipisci elLorem ipsum dolor sit amet, consectetur adipisci', 0, 0),
(3, 'Escape 1', 'ER', NULL, '18:00:00', '26:00:00', 1, 0, 0, 'red', 0, 'logo.jpg', 'Lorem ipsum dolor sit amet, consectetur adipisci elLorem ipsum dolor sit amet, consectetur adipisci', 0, 30),
(4, 'Antartide', 'ER', 'ERHR', '18:00:00', '26:00:00', 1, 0, 0, 'red', 0, 'logo.jpg', 'Lorem ipsum dolor sit amet, consectetur adipisci elLorem ipsum dolor sit amet, consectetur adipisci', 0, 30),
(5, 'Saw Room', 'ER', 'ERHR', '16:00:00', '26:00:00', 0, 8, 0, 'red', 0, 'logo.jpg', 'Lorem ipsum dolor sit amet, consectetur adipisci elLorem ipsum dolor sit amet, consectetur adipisci', 0, 30);

ALTER TABLE `giorni`
  ADD PRIMARY KEY (`IDGiorno`),
  ADD KEY `FKRoom` (`FKRoom`),
  ADD KEY `FKApertura` (`FKApertura`);
  
  ALTER TABLE `room`
  ADD PRIMARY KEY (`IDRoom`);

ALTER TABLE `giorni`
  MODIFY `IDGiorno` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT per la tabella `room`
--
ALTER TABLE `room`
  MODIFY `IDRoom` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;


select * from giorni


IDGiorno
DayofWeek
Nome
Aperto
OrarioApertura
OrarioChiusura
FKRoom
FKApertura


1
2
Lun
0
09:00:00
19:00:00
1
1

2
3
Mar
0
09:00:00
19:00:00
1
1

3
4
Mer
0
09:00:00
19:00:00
1
1

4
5
Gio
0
09:00:00
19:00:00
1
1

5
6
Ven
0
09:00:00
19:00:00
1
1

6
7
Sab
0
09:00:00
19:00:00
1
1

7
1
Sun
0
09:00:00
19:00:00
1
1


 SELECT IDRoom,Nomeroom,Coperto,Statobloccato,Aperto,OrarioApertura,OrarioChiusura,FKRoom, Nome
  FROM room
  INNER JOIN giorni ON (room.IDRoom = giorni.FKRoom)


IDRoom
Nomeroom
Coperto
Statobloccato
Aperto
OrarioApertura
OrarioChiusura
FKRoom
Nome


1
Tennis 1
0
0
0
09:00:00
19:00:00
1
Lun

1
Tennis 1
0
0
0
09:00:00
19:00:00
1
Mar

1
Tennis 1
0
0
0
09:00:00
19:00:00
1
Mer

1
Tennis 1
0
0
0
09:00:00
19:00:00
1
Gio

1
Tennis 1
0
0
0
09:00:00
19:00:00
1
Ven

1
Tennis 1
0
0
0
09:00:00
19:00:00
1
Sab

1
Tennis 1
0
0
0
09:00:00
19:00:00
1
Sun


UPDATE room r JOIN
       giorni g
       ON r.IDRoom = g.FKRoom
SET r.StatoBloccato = false, 
    g.Aperto = true, 
    g.OrarioApertura = '09:00:00', 
    g.OrarioChiusura = '18:00:00' 
WHERE r.IDRoom = 1 AND g.Nome LIKE 'Lun%';
 SELECT IDRoom,Nomeroom,Coperto,Statobloccato,Aperto,OrarioApertura,OrarioChiusura,FKRoom
  FROM room
  INNER JOIN giorni ON (room.IDRoom = giorni.FKRoom)


IDRoom
Nomeroom
Coperto
Statobloccato
Aperto
OrarioApertura
OrarioChiusura
FKRoom


1
Tennis 1
0
0
1
09:00:00
18:00:00
1

1
Tennis 1
0
0
0
09:00:00
19:00:00
1

1
Tennis 1
0
0
0
09:00:00
19:00:00
1

1
Tennis 1
0
0
0
09:00:00
19:00:00
1

1
Tennis 1
0
0
0
09:00:00
19:00:00
1

1
Tennis 1
0
0
0
09:00:00
19:00:00
1

1
Tennis 1
0
0
0
09:00:00
19:00:00
1

IDGiorno	DayofWeek	Nome	OrarioApertura	OrarioChiusura	FKRoom	FKApertura
1	2	Lun	09:00:00	19:00:00	1	1
2	3	Mar	09:00:00	19:00:00	1	1
3	4	Mer	09:00:00	19:00:00	1	1
4	5	Gio	09:00:00	19:00:00	1	1
5	6	Ven	09:00:00	19:00:00	1	1
6	7	Sab	09:00:00	19:00:00	1	1
7	1	Sun	09:00:00	19:00:00	1	1

IDRoom	Nomeroom	OrarioApertura	OrarioChiusura	FKRoom	Nome
1	Tennis 1	09:00:00	19:00:00	1	Lun
1	Tennis 1	09:00:00	19:00:00	1	Mar
1	Tennis 1	09:00:00	19:00:00	1	Mer
1	Tennis 1	09:00:00	19:00:00	1	Gio
1	Tennis 1	09:00:00	19:00:00	1	Ven
1	Tennis 1	09:00:00	19:00:00	1	Sab
1	Tennis 1	09:00:00	19:00:00	1	Sun

IDRoom	Nomeroom	Aperto	OrarioApertura	OrarioChiusura	FKRoom
1	Tennis 1	1	09:00:00	18:00:00	1
1	Tennis 1	0	09:00:00	19:00:00	1
1	Tennis 1	0	09:00:00	19:00:00	1
1	Tennis 1	0	09:00:00	19:00:00	1
1	Tennis 1	0	09:00:00	19:00:00	1
1	Tennis 1	0	09:00:00	19:00:00	1
1	Tennis 1	0	09:00:00	19:00:00	1