MySQL 8.0

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

-- create sample data

CREATE TABLE main (
  main_id INT PRIMARY KEY,
  main_name VARCHAR(100)
  )
SELECT 1 main_id, 'Name 1' main_name UNION ALL
SELECT 2, 'Name 2' UNION ALL
SELECT 3, 'Name 2';
CREATE TABLE slave (
  slave_id INT PRIMARY KEY,
  main_id INT NOT NULL,
  slave_name VARCHAR(100),
  FOREIGN KEY (main_id) REFERENCES main (main_id) ON DELETE CASCADE ON UPDATE CASCADE
  );
INSERT INTO slave
SELECT 1, 1, 'Name 1-1' UNION ALL
SELECT 2, 1, 'Name 1-2' UNION ALL
SELECT 3, 2, 'Name 2-1' UNION ALL
SELECT 4, 2, 'Name 2-2' UNION ALL
SELECT 5, 1, 'Name 1-3' UNION ALL
SELECT 6, 3, 'Name 2(3)-1' UNION ALL
SELECT 7, 3, 'Name 2(3)-3' UNION ALL
SELECT 8, 3, 'Name 2(3)-2' UNION ALL
SELECT 9, 2, 'Name 2-3';
SELECT * FROM main;
SELECT * FROM slave;
SELECT * FROM main JOIN slave USING (main_id);

Records: 3  Duplicates: 0  Warnings: 0



Records: 9  Duplicates: 0  Warnings: 0




main_id
main_name


1
Name 1

2
Name 2

3
Name 2




slave_id
main_id
slave_name


1
1
Name 1-1

2
1
Name 1-2

3
2
Name 2-1

4
2
Name 2-2

5
1
Name 1-3

6
3
Name 2(3)-1

7
3
Name 2(3)-3

8
3
Name 2(3)-2

9
2
Name 2-3




main_id
main_name
slave_id
slave_name


1
Name 1
1
Name 1-1

1
Name 1
2
Name 1-2

1
Name 1
5
Name 1-3

2
Name 2
3
Name 2-1

2
Name 2
4
Name 2-2

2
Name 2
9
Name 2-3

3
Name 2
6
Name 2(3)-1

3
Name 2
7
Name 2(3)-3

3
Name 2
8
Name 2(3)-2


-- merge data based on main.main_name

UPDATE slave
  JOIN main m1 USING (main_id)
  JOIN ( SELECT MIN(main_id) main_id, main_name
         FROM main m2
         GROUP BY 2
         ) m3 USING (main_name)
SET slave.main_id = m3.main_id;

SELECT * FROM main;
SELECT * FROM slave;
SELECT * FROM main JOIN slave USING (main_id);

Rows matched: 9  Changed: 3  Warnings: 0




main_id
main_name


1
Name 1

2
Name 2

3
Name 2




slave_id
main_id
slave_name


1
1
Name 1-1

2
1
Name 1-2

3
2
Name 2-1

4
2
Name 2-2

5
1
Name 1-3

6
2
Name 2(3)-1

7
2
Name 2(3)-3

8
2
Name 2(3)-2

9
2
Name 2-3




main_id
main_name
slave_id
slave_name


1
Name 1
1
Name 1-1

1
Name 1
2
Name 1-2

1
Name 1
5
Name 1-3

2
Name 2
3
Name 2-1

2
Name 2
4
Name 2-2

2
Name 2
6
Name 2(3)-1

2
Name 2
7
Name 2(3)-3

2
Name 2
8
Name 2(3)-2

2
Name 2
9
Name 2-3


-- remove rows w/o references from main table

DELETE FROM main
WHERE NOT EXISTS (
  SELECT NULL
  FROM slave
  WHERE main.main_id = slave.main_id
  );

SELECT * FROM main;
SELECT * FROM slave;
SELECT * FROM main JOIN slave USING (main_id);


main_id
main_name


1
Name 1

2
Name 2




slave_id
main_id
slave_name


1
1
Name 1-1

2
1
Name 1-2

3
2
Name 2-1

4
2
Name 2-2

5
1
Name 1-3

6
2
Name 2(3)-1

7
2
Name 2(3)-3

8
2
Name 2(3)-2

9
2
Name 2-3




main_id
main_name
slave_id
slave_name


1
Name 1
1
Name 1-1

1
Name 1
2
Name 1-2

1
Name 1
5
Name 1-3

2
Name 2
3
Name 2-1

2
Name 2
4
Name 2-2

2
Name 2
6
Name 2(3)-1

2
Name 2
7
Name 2(3)-3

2
Name 2
8
Name 2(3)-2

2
Name 2
9
Name 2-3

slave_id	main_id	slave_name
1	1	Name 1-1
2	1	Name 1-2
3	2	Name 2-1
4	2	Name 2-2
5	1	Name 1-3
6	3	Name 2(3)-1
7	3	Name 2(3)-3
8	3	Name 2(3)-2
9	2	Name 2-3

main_id	main_name	slave_id	slave_name
1	Name 1	1	Name 1-1
1	Name 1	2	Name 1-2
1	Name 1	5	Name 1-3
2	Name 2	3	Name 2-1
2	Name 2	4	Name 2-2
2	Name 2	9	Name 2-3
3	Name 2	6	Name 2(3)-1
3	Name 2	7	Name 2(3)-3
3	Name 2	8	Name 2(3)-2