Insert data from another table

Databases
1

I have a table called “service_items” and another table called “service_images”. The service_images table contains the filename of each service item and has the following 2 relevant fields:

service_item_id
filename

The service_items table has a unique id field named “id”

So I guess I need to somehow loop through every record in the “service_images” table and for each record insert the filename into the matching record in the “service_items” table.

I am not sure how to achieve this so would appreciate some help with the query.

Thanks

Paul

2

Hmm I think the following may do it?



UPDATE service_items, service_images SET service_items.filename = service_images.filename WHERE service_images.service_item_id = service_items.id
3

Perhaps something like this?



INSERT IGNORE INTO `service_items`
SELECT service_item_id, filename
FROM `service_images` WHERE service_item_id = id

Or this



INSERT INTO service_items SELECT filename, service_item_id FROM service_images
WHERE service_items.id = service_images.service_item_id UPDATE service_items.filename = service_images.filename

I am probably stabbing in the dark here