MySQL 5.5

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

select version();


version()


5.5.62


CREATE TABLE dotable1 (user VARCHAR(50),access_date DATE,blocked CHAR(1));

INSERT INTO dotable1 (user, access_date, blocked) VALUES
('Will', '2023-07-12', 'Y'),
('Will', '2023-07-13', 'Y'),
('Will', '2023-07-14', 'Y'),
('Will', '2023-07-28', 'Y');

CREATE TABLE dotable2 (date1 DATE,date2 DATE, user VARCHAR(50),access_denied INT);

INSERT INTO dotable2 (date1, date2, user, access_denied) VALUES
('2023-02-08', '2023-02-12', 'Will', NULL),
('2023-07-12', '2023-07-28', 'Will', NULL),
('2021-06-18', '2021-06-26', 'Will', NULL);

Records: 4  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0


SELECT  * FROM dotable1;


user
access_date
blocked


Will
2023-07-12
Y

Will
2023-07-13
Y

Will
2023-07-14
Y

Will
2023-07-28
Y


SELECT  * FROM dotable2;


date1
date2
user
access_denied


2023-02-08
2023-02-12
Will
null

2023-07-12
2023-07-28
Will
null

2021-06-18
2021-06-26
Will
null


SELECT emr.date1,emr.date2,asr.user, COUNT(*) AS access_denied
FROM dotable1 asr
INNER JOIN dotable2 emr ON
asr.user = emr.user AND asr.access_date BETWEEN emr.date1 AND emr.date2
WHERE
asr.user = 'Will' AND asr.blocked = 'Y'
GROUP BY
emr.date1, emr.date2, asr.user


date1
date2
user
access_denied


2023-07-12
2023-07-28
Will
4


UPDATE dotable2 emr
INNER JOIN (
SELECT emr.date1,emr.date2,asr.user, COUNT(*) AS access_denied
FROM dotable1 asr
INNER JOIN dotable2 emr ON
asr.user = emr.user AND asr.access_date BETWEEN emr.date1 AND emr.date2
WHERE
asr.user = 'Will' AND asr.blocked = 'Y'
GROUP BY
emr.date1, emr.date2, asr.user
) AS temp
ON
emr.date1 = temp.date1 AND emr.date2 = temp.date2 AND emr.user = temp.user
SET
emr.access_denied = temp.access_denied;

Rows matched: 1  Changed: 1  Warnings: 0


SELECT  * FROM dotable2;


date1
date2
user
access_denied


2023-02-08
2023-02-12
Will
null

2023-07-12
2023-07-28
Will
4

2021-06-18
2021-06-26
Will
null

user	access_date	blocked
Will	2023-07-12	Y
Will	2023-07-13	Y
Will	2023-07-14	Y
Will	2023-07-28	Y

date1	date2	user	access_denied
2023-02-08	2023-02-12	Will	null
2023-07-12	2023-07-28	Will	null
2021-06-18	2021-06-26	Will	null