I matched many answers about copying data from one table to another. However how to use a clause in addition to the classical way:
INSERT INTO T2 (field1, field2) SELECT field1, field2 FROM T1;
I want to copy X fields from T1 to T2 but where T2.T1_id = T1.id.
Something like:
INSERT INTO bottles (volume_id, container_id) SELECT volume_id, container_id FROM wines, bottles WHERE bottles.wine_id = wines.id
In fact, I want to move volume_id, container_id from wines to bottles, but for bottles corresponding to each wine.
Does I need an UPDATE instead ?
Something LIKE this is possible ?
INSERT INTO bottles (volume_id, container_id) VALUES (SELECT volume_id, container_id FROM wines, bottles WHERE bottles.wine_id = wines.id) WHERE bottles.wine_id = wines.id
Thanks !
EDIT:
Example current data:
table wines
-----------
id: 1
volume_id: 14
container_id:3
-
id: 2
volume_id: 15
container_id:4
table bottles
-------------
id: 1
wine_id: 1
-
id:2
wine_id: 1
-
id:3
wine_id: 2
New wanted data:
table wines
-----------
id: 1
...
-
id: 2
...
table bottles
-------------
id: 1
wine_id: 1
volume_id: 14
container_id:3
-
id:2
wine_id: 1
volume_id: 14
container_id:3
-
id:3
wine_id: 2
volume_id: 15
container_id:4