Hey all,
Have a query here to select Prices from a table to see what they are assigned to ... its the lovely ZenCart...
However the customer has a lot of products which were added in bulk its very custom setup.
What I have is a need to update the prices via SQL and give him a page to be able to change them at his wish.
The Select Code is
SELECT
`p`.`products_id`,
`products_attributes`.`options_values_price`,
`products_options`.`products_options_name`
FROM
`products` AS `p`
Inner Join `products_to_categories` AS `p2c` ON `p`.`products_id` = `p2c`.`products_id`
Inner Join `categories_description` AS `cd` ON `cd`.`categories_id` = `p2c`.`categories_id`
Inner Join `categories` AS `c` ON `c`.`categories_id` = `p2c`.`categories_id`
Inner Join `products_attributes` ON `products_attributes`.`products_id` = `p`.`products_id`
Inner Join `products_options` ON `products_options`.`products_options_id` = `products_attributes`.`options_id`
WHERE
`cd`.`short_name` LIKE '%NAMED' AND
`products_options`.`products_options_name` = ' MP3'
What I need to do is update products_attributes.options_values_price where cd.short_name LIKE %NAMED and as the SQL says products_options_name=blah blah MP3.
Been trying using navicat to build the query to UPDATE ... SET ... =3.99 WHERE (ABOVE SQL) however it wont let me error "ERROR 1093 (HY000): You can't specify target table 'pa' for update in FROM clause"
Any ideas?
Thanks
