SQL Query to duplicate records based on If statement
- by user328371
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.
