Move one column from one table to another mysql

Question

I have two tables, Products and Images. The Products table has an "image" column, I want to move the images inside said column to the Images table. The Images table has column "product_id", that's how both tables are related

Products table

id
image

Images Table

id
image
product_id

The ID from the Products table should work as a foreign key in the Images table under product_id for image, it should works the same as a primary key in Products table as for images. One more thing, Images Table is not empty it already has data in it.

Possible duplicate of [MySQL - UPDATE query based on SELECT Query](https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query) — Aleks G, Jul 12 '19 at 13:13
Are you asking us for a query that will move the contents of `Product.image` to `Images.image` — RiggsFolly, Jul 12 '19 at 13:14
I assume there are no rows yet in the Images table... right? — RiggsFolly, Jul 12 '19 at 13:15
yes @RiggsFolly and no images table already has data under both product_id and image column — Ali Raza, Jul 12 '19 at 13:22

score 2 · Accepted Answer · answered Jul 12 '19 at 13:30

2

You can use INSERT INTO with raw data, but you can also combine it with a SELECT query.

For the new Id values on your Images table, you can use the existing Product.Id values or generate new ones with a function like UUID(), which I would recommend because it makes longer more unique IDs that can be globally unique.

Something like this will probably work:

INSERT INTO Images (id, image, product_id)
SELECT UUID(), image, id FROM Products;

answered Jul 12 '19 at 13:30

Matthew Poer

1,682
10
17

ok i get it. thank you... now i should simply drop the image table from products – Ali Raza Jul 12 '19 at 13:41

Move one column from one table to another mysql

1 Answers1