MySQL 8.0

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


VERSION()


8.0.27


DROP TABLE IF EXISTS
  `empleados_dptos_XX`,
  `dptos_relacion_XX`;

DROP PROCEDURE IF EXISTS
  `empleados_relacionados`;

CREATE TABLE IF NOT EXISTS `dptos_relacion_XX` (
  `id` SERIAL,
  `dpto` INT NOT NULL
);

CREATE TABLE IF NOT EXISTS `empleados_dptos_XX` (
  `id` SERIAL,
  `dpto` INT NOT NULL
);

INSERT INTO `dptos_relacion_XX`
  (`dpto`)
VALUES
  (1), (2), (3), (4), (5);

INSERT INTO `empleados_dptos_XX`
  (`dpto`)
VALUES
  (1), (1), (1),
  (2), (2), (2),
  (3), (3), (3),
  (4), (4), (4),
  (5), (5), (5);

CREATE PROCEDURE `empleados_relacionados` (
  `pais_name` VARCHAR(2)
)
BEGIN
  /* Borrar registros de ese departamento */ 
  SET @`borrar` :=
    CONCAT('
      DELETE FROM
        `dptos_relacion_', `pais_name`, '` ',
      'WHERE ',
      '  `dpto` = (
           SELECT
             `dpto`
           FROM
             `empleados_dptos_', `pais_name`, '` ',
           'ORDER BY ',
           '  `id` DESC ',
           'LIMIT 0, 1
         );
    ');

  PREPARE `borrar` FROM @`borrar`;
  EXECUTE `borrar`;
  DEALLOCATE PREPARE `borrar`;
END;
CALL `empleados_relacionados`('XX');

SELECT
  `id`,
  `dpto`
FROM
  `dptos_relacion_XX`;

SELECT
  `id`,
  `dpto`
FROM
  `empleados_dptos_XX`;


id
dpto


1
1

2
2

3
3

4
4




id
dpto


1
1

2
1

3
1

4
2

5
2

6
2

7
3

8
3

9
3

10
4

11
4

12
4

13
5

14
5

15
5