Kryptronic Software Support Forum
You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.
#1 07-15-2016 05:21:50
SQL add products xcat values into another product
Hi all
I'm going through my products and updating them individually. I want to be able to enter a RAW SQL statement that would add the related categories (xcat) from one of my products into another products related categories, rather than amend each product in turn and clicking on each related category as I go (some products are in 6+ categories and I have a lot of categories).
Many thanks
Terry
Offline
#2 07-15-2016 07:25:23
Re: SQL add products xcat values into another product
Assuming product with id 'AAA' has the category assignments you want to pull, and you want to change the product with id 'BBB' to use those same assignments. Basically copying ecom_prod.xcat from id='AAA' to ecom_prod.xcat where id='BBB'.
UPDATE ecom_prod SET xcat=(SELECT xcat FROM ecom_prod WHERE id='AAA') WHERE id='BBB'
Nick Hendler
Offline
#4 07-15-2016 10:49:45
Re: SQL add products xcat values into another product
Just to let you all know that MySQL doesn’t allow referring to a table that’s targeted for update in a FROM clause, which is what Nick suggested.
The workaround for MySQL:
UPDATE ecom_prod SET xcat=(SELECT xcat FROM (select * FROM ecom_prod) as x WHERE id='AAA') WHERE id='BBB'
Since MySQL materializes subqueries in the FROM clause (“derived tables”) as temporary tables, wrapping the subquery into another inner subquery in the FROM clause causes it to be executed and stored into a temporary table, then referenced implicitly in the outer subquery.
For those that want a fuller explanation then please visit
Terry
Offline
#5 07-18-2016 07:34:04
Re: SQL add products xcat values into another product
Thanks for the post back with the SQL. I'm pretty sure what I posted will run on most databases, but yours is certainly valid and written well.
Nick Hendler
Offline