加入MySQL信息和2个表

Question

Hello StackOverflow members, I need your help to do the following.

I have 2 tables in MySQL database:

Table: products

• product_id
• name
• long_desc
• price
• id_categorie
• manufacturer
• images

Table: images

• image_id
• path
• id_product

Now I need to copy the contents of PATH COLUMN from IMAGES TABLE to PATH COLUMN in PRODUCTS TABLE BUT where id_product = product_id.

Lets say I have some data in PRODUCTS TABLE and IMAGES TABLE.

PRODUCTS

• product_id: 74
• name: Product1
• long_desc: SomeText
• price: 100.00
• id_categorie: 3
• manufacturer: Panasonic
• images: NULL.

IMAGES

• image_id: 1
• path: ./images/panasonic.jpg
• id_product: 74

I need to copy the PATH from IMAGES to IMAGES in PRODUCTS WHERE id_products from IMAGES = product_id from PRODUCTS.

The problem is I have 2500 products and I don't have time to do it manually so I need some help with doing this. Can this be done with JOIN or PHP? Need some help with this. =)

Thanks

Answer 1

I think this update should work

UPDATE `products` `p` INNER JOIN `images` `i` ON `i`.`id_product` = `p`.`product_id` SET `p`.`images` = `i`.`path`;