Forums / Setting Up Categories, Products, Attributes / How do I use SQL to update many categories_id values in products_to_categories?

How do I use SQL to update many categories_id values in products_to_categories?

Locked

Started by molywerks on 9 Nov 2009, 4:00 PM · Views: 757

Results 1 to 2 of 2
This thread is locked. New replies are disabled.
9 Nov 2009, 4:00 PM
#1
molywerks avatar

molywerks

Zen Follower

Join Date:
Aug 2006
Posts:
136
Plugin Contributions:
0

How do I use SQL to update many categories_id values in products_to_categories?

Within the products_to_categories table, I need to change too many categories_id values from 25 to 26. It would be easy if ALL 25s were to be changed but only some of them need to be changed. I thought I had done this before because I have in my "working mysql scripts" folder the following snippet of code:

UPDATE zen_products_to_categories SET categories_id = '26' WHERE (products_id, categories_id) = '434', '25';
UPDATE zen_products_to_categories SET categories_id = '26' WHERE (products_id, categories_id) = '435', '25';
UPDATE zen_products_to_categories SET categories_id = '26' WHERE (products_id, categories_id) = '439', '25';

Unfortunately, I am getting the following SQL error message:

Error

SQL query:

UPDATE zen_products_to_categories SET categories_id = '26' WHERE (
products_id,
categories_id
) = '434',
'25';

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '25'' at line 1

What is the proper SQL code (MySQL version is "5.0.81-community")? Do I need to include database/table names in some manner and if so, how do I do that again?

Thank you!

9 Nov 2009, 4:16 PM
#2
molywerks avatar

molywerks

Zen Follower

Join Date:
Aug 2006
Posts:
136
Plugin Contributions:
0

Re: How do I use SQL to update many categories_id values in products_to_categories?

Yargh. Duh. Durr. Had I simply used PhpMyAdmin to edit a couple of these, I could have learned the proper syntax earlier. Here is the answer to my above question:

UPDATE zendb_zen.zen_products_to_categories SET categories_id = '26' WHERE live_products_to_categories.products_id =434 AND live_products_to_categories.categories_id =25 LIMIT 1 ;

UPDATE zendb_zen.zen_products_to_categories SET categories_id = '26' WHERE live_products_to_categories.products_id =435 AND live_products_to_categories.categories_id =25 LIMIT 1 ;

UPDATE zendb_zen.zen_products_to_categories SET categories_id = '26' WHERE live_products_to_categories.products_id =439 AND live_products_to_categories.categories_id =25 LIMIT 1 ;