Horst Jahns Horst Jahns - 2 months ago 11
SQL Question

Normalization - Migrate Data to Another Table

I have a table

template
with the field
json
. Since
json
can be the same for many
template
(1:n) I created another table
template_json
and added the field
template_json_id
(FK) to
template
.

To migrate all data from one table to antoher I used following SQL:

INSERT INTO db.template_json (`json`)
SELECT `json`
FROM db.template;


This migrates the data perfectly, but of course leaves my
template.template_json_id
empty. I would need to update each row's
template.template_json_id
(FK) with
template_json.id
from the insert.

Is it possible to do this in one query?

Answer

If you don't have duplicate you can use and update with inner join

update template
inner join template on template_json.json = template.json
set template.template_json_id = template_json.id;

if you have duplicate you should populate with a select distinct

INSERT INTO db.template_json (`json`)
SELECT distinct `json`
FROM db.template;
Comments