MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();


VERSION()


8.0.22


DROP TABLE IF EXISTS `liturgia_breviario`, `liturgia_horas_lpatristicas`;

CREATE TABLE IF NOT EXISTS `liturgia_horas_lpatristicas` (
  `id_patristica` BIGINT NOT NULL,
  `id_liturgia` VARCHAR(10),
  `id_breviario` BIGINT
);

CREATE TABLE IF NOT EXISTS `liturgia_breviario` (
  `id_breviario` BIGINT not null,
  `id_tiempo` INT,
  `id_ciclo` INT,
  `id_semana` INT,
  `id_dia` INT
);

INSERT INTO
  `liturgia_horas_lpatristicas`
VALUES
  (76589, '0708310401', null),
  (76590, '0708310501', null);

INSERT INTO
  `liturgia_breviario`
VALUES
  (1, 7, 8, 31, 4),
  (2, 7, 8, 31, 5),
  (3, 7, 8, 31, 6);
SELECT
  `id_patristica`,
  `id_liturgia`,
  `id_breviario`
FROM
  `liturgia_horas_lpatristicas`;


id_patristica
id_liturgia
id_breviario


76589
0708310401
null

76590
0708310501
null


UPDATE
  `liturgia_horas_lpatristicas`
  INNER JOIN (
    SELECT
      `liturgia_breviario`.`id_breviario`,
      GROUP_CONCAT(
        LPAD(`liturgia_breviario`.`id_tiempo`, 2, 0),
        LPAD(`liturgia_breviario`.`id_ciclo`, 2, 0),
        LPAD(`liturgia_breviario`.`id_semana`, 2, 0),
        LPAD(`liturgia_breviario`.`id_dia`, 2, 0)
      ) `grupo_liturgia_breviario`
    FROM
      `liturgia_breviario`
    GROUP BY 
      `liturgia_breviario`.`id_breviario`
  ) `der`
SET
  `liturgia_horas_lpatristicas`.`id_breviario` = `der`.`id_breviario`
WHERE
  LEFT(`liturgia_horas_lpatristicas`.`id_liturgia`, 8) = `der`.`grupo_liturgia_breviario`;
SELECT
  `id_patristica`,
  `id_liturgia`,
  `id_breviario`
FROM
  `liturgia_horas_lpatristicas`;


id_patristica
id_liturgia
id_breviario


76589
0708310401
1

76590
0708310501
2