SQL Query to duplicate records based on If statement

Question

Hi,

I'm trying to write an SQL query that will duplicate records depending on a field in another table. I am running mySQL 5.

(I know duplicating records shows that the database structure is bad, but I did not design the database and am not in a position to redo it all - it's a shopp ecommerce database running on wordpress.)

Each product with a particular attribute needs a link to the same few images, so the product will need a row per image in a table - the database doesn't actually contain the image, just its filename. (the images are of clipart for a customer to select from)

Based on these records...

SELECT * FROM `wp_shopp_spec` WHERE name='Can Be Personalised' and content='Yes'

I want to do something like this..

For each record that matches that query, copy records 5134 - 5139 from wp_shopp_asset but change the id so it's unique and set the cell in column 'parent' to have the value of 'product' from the table wp_shopp_spec. This will mean 6 new records are created for each record matching the above query, all with the same value in 'parent' but with unique ids and every other column copied from the original (ie. records 5134-5139)

Hope that's clear enough - any help greatly appreciated.

Answer 1

It sounds like you need to cross join to the asset values you want. So something like:

Select spec.parent, asset.id as uniqueid, ...
From`wp_shopp_spec` spec
    Cross Join  (
                Select id
                From `wp_shopp_asset`
                Where Id Between 5134 And 5139
                ) asset
Where name='Can Be Personalised' 
    And content='Yes'

Answer 2

INSERT wp_shopp_asset
(parent, col1, col2, ...)
SELECT wp_shopp_spec.product, wp_shopp_asset.col1, wp_shopp_asset.col2, ...
FROM wp_shopp_spec
CROSS JOIN wp_shopp_asset
WHERE wp_shopp_spec.name='Can Be Personalised'
  AND wp_shopp_spec.content='Yes'
  AND wp_shopp_asset.id BETWEEN 5134 AND 5139