MySQL 8.0

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

CREATE TABLE risto_prenota (id INT, sala VARCHAR(255), tavolo INT, data DATE, valore INT, id_prenotazione INT);
INSERT INTO risto_prenota VALUES
(9993, 'ROMA', 1, '2022-06-11', 1, NULL),
(9994, 'ROMA', 1, '2022-06-12', 1, NULL),
(9995, 'ROMA', 1, '2022-06-13', 1, NULL),
(9996, 'ROMA', 1, '2022-06-14', 1, NULL),
(9997, 'ROMA', 1, '2022-06-15', 1, NULL),
(99933, 'ROMA2', 1, '2022-06-11', 1, NULL),
(99944, 'ROMA2', 1, '2022-06-12', 1, NULL),
(99955, 'ROMA2', 1, '2022-06-13', 1, NULL),
(99966, 'ROMA2', 1, '2022-06-14', 1, NULL),
(99977, 'ROMA2', 1, '2022-06-15', 1, NULL),
(99938, 'ROMA2', 2, '2022-06-11', 1, NULL),
(99949, 'ROMA2', 2, '2022-06-12', 1, NULL),
(99951, 'ROMA2', 2, '2022-06-13', 1, NULL),
(99962, 'ROMA2', 2, '2022-06-14', 1, NULL),
(99973, 'ROMA2', 2, '2022-06-15', 1, NULL);
SELECT * FROM risto_prenota;


id
sala
tavolo
data
valore
id_prenotazione


9993
ROMA
1
2022-06-11
1
null

9994
ROMA
1
2022-06-12
1
null

9995
ROMA
1
2022-06-13
1
null

9996
ROMA
1
2022-06-14
1
null

9997
ROMA
1
2022-06-15
1
null

99933
ROMA2
1
2022-06-11
1
null

99944
ROMA2
1
2022-06-12
1
null

99955
ROMA2
1
2022-06-13
1
null

99966
ROMA2
1
2022-06-14
1
null

99977
ROMA2
1
2022-06-15
1
null

99938
ROMA2
2
2022-06-11
1
null

99949
ROMA2
2
2022-06-12
1
null

99951
ROMA2
2
2022-06-13
1
null

99962
ROMA2
2
2022-06-14
1
null

99973
ROMA2
2
2022-06-15
1
null


-- Set info for booking
SET @from:='2022-06-11';
SET @till:='2022-06-13';
SET @id:=111;
-- Select all (sala, tavolo) pairs available for booking
-- Only as explanation
SELECT sala, tavolo
FROM risto_prenota
WHERE data BETWEEN @from AND @till
GROUP BY 1, 2
HAVING NOT SUM(id_prenotazione IS NOT NULL)
-- LIMIT 1
;


sala
tavolo


ROMA
1

ROMA2
1

ROMA2
2


-- Perform booking
UPDATE risto_prenota
JOIN (
    SELECT sala, tavolo
    FROM risto_prenota
    WHERE data BETWEEN @from AND @till
    GROUP BY 1, 2
    HAVING NOT SUM(id_prenotazione IS NOT NULL)
    LIMIT 1
) updated_data USING (sala, tavolo)
SET risto_prenota.id_prenotazione = @id, 
    risto_prenota.valore = 0
WHERE risto_prenota.data BETWEEN @from AND @till;
-- Check result
SELECT * FROM risto_prenota;


id
sala
tavolo
data
valore
id_prenotazione


9993
ROMA
1
2022-06-11
0
111

9994
ROMA
1
2022-06-12
0
111

9995
ROMA
1
2022-06-13
0
111

9996
ROMA
1
2022-06-14
1
null

9997
ROMA
1
2022-06-15
1
null

99933
ROMA2
1
2022-06-11
1
null

99944
ROMA2
1
2022-06-12
1
null

99955
ROMA2
1
2022-06-13
1
null

99966
ROMA2
1
2022-06-14
1
null

99977
ROMA2
1
2022-06-15
1
null

99938
ROMA2
2
2022-06-11
1
null

99949
ROMA2
2
2022-06-12
1
null

99951
ROMA2
2
2022-06-13
1
null

99962
ROMA2
2
2022-06-14
1
null

99973
ROMA2
2
2022-06-15
1
null


-- Set info for booking
SET @from:='2022-06-11';
SET @till:='2022-06-15';
SET @id:=112;
-- Perform booking
UPDATE risto_prenota
JOIN (
    SELECT sala, tavolo
    FROM risto_prenota
    WHERE data BETWEEN @from AND @till
    GROUP BY 1, 2
    HAVING NOT SUM(id_prenotazione IS NOT NULL)
    LIMIT 1
) updated_data USING (sala, tavolo)
SET risto_prenota.id_prenotazione = @id, 
    risto_prenota.valore = 0
WHERE risto_prenota.data BETWEEN @from AND @till;
-- Check result
SELECT * FROM risto_prenota;


id
sala
tavolo
data
valore
id_prenotazione


9993
ROMA
1
2022-06-11
0
111

9994
ROMA
1
2022-06-12
0
111

9995
ROMA
1
2022-06-13
0
111

9996
ROMA
1
2022-06-14
1
null

9997
ROMA
1
2022-06-15
1
null

99933
ROMA2
1
2022-06-11
0
112

99944
ROMA2
1
2022-06-12
0
112

99955
ROMA2
1
2022-06-13
0
112

99966
ROMA2
1
2022-06-14
0
112

99977
ROMA2
1
2022-06-15
0
112

99938
ROMA2
2
2022-06-11
1
null

99949
ROMA2
2
2022-06-12
1
null

99951
ROMA2
2
2022-06-13
1
null

99962
ROMA2
2
2022-06-14
1
null

99973
ROMA2
2
2022-06-15
1
null

id	sala	tavolo	data	valore	id_prenotazione
9993	ROMA	1	2022-06-11	1	null
9994	ROMA	1	2022-06-12	1	null
9995	ROMA	1	2022-06-13	1	null
9996	ROMA	1	2022-06-14	1	null
9997	ROMA	1	2022-06-15	1	null
99933	ROMA2	1	2022-06-11	1	null
99944	ROMA2	1	2022-06-12	1	null
99955	ROMA2	1	2022-06-13	1	null
99966	ROMA2	1	2022-06-14	1	null
99977	ROMA2	1	2022-06-15	1	null
99938	ROMA2	2	2022-06-11	1	null
99949	ROMA2	2	2022-06-12	1	null
99951	ROMA2	2	2022-06-13	1	null
99962	ROMA2	2	2022-06-14	1	null
99973	ROMA2	2	2022-06-15	1	null