need help creating a mysql 5.0.95 query

[jae ahn]

just to put it out there, i really have no experience with sql. i took some computer science classes in high school and that's it... i can mess with some html but also very little... i usually google and try and teach myself if there is anything i need to do...

but this is beyond me...

and i'm also not familiar with sql lingo... so i'll try the best i can at explaining what i need...

here's a little background, i figured out this code because i needed to change the categories_id from one number to a different number... you can ignore the 'to category value' and 'from category value' ... those are just notes to myself when i need to use this code again...

Quote :
UPDATE products_to_categories SET categories_id='To category value' WHERE categories_id='from category value'
now i need to do something a little more complex and i can't figure it out...

i need to kind of incorporate this code below in what i'm trying to do next...
Quote :
SELECT * FROM `products_description` WHERE `products_name` LIKE 'ABC%'
ok... now on with what i need to do...

i need to search within products_description and find products_name that start with ABC... as shown above... that will give me a list of products that start with ABC

products_to_categories... which i believe is called a table... (then products_description would also be considered a table)... i need to search within this table and find all the products from the list above... i need to find all the products that start with ABC... but the problem is that inside this table, products_name doesn't exist... instead each product has a product_id... and this same id number is also present in the table products_description

after getting this crazy list, i need to change the categories_id of all the ones that start with ABC to a different caterogies_id...

---------------------

products_description - has this information ( products_name and products_id )

products_to_categories - has this information ( products_id and categories_id )

-----------------------

all the products that start with ABC are set to a category called 123... i need to change that category to 987


i'm sorry if this is confusing but i hope someone can help.


here is some additional information regarding the indexes.. i'm sorry it's not in a very friendly format

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
products_to_categories 0 PRIMARY 1 products_id A 28799 NULL NULL BTREE
products_to_categories 0 PRIMARY 2 categories_id A 28799 NULL NULL BTREE

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
products_description 0 PRIMARY 1 products_id A 23202 NULL NULL BTREE
products_description 0 PRIMARY 2 language_id A 23202 NULL NULL BTREE
products_description 1 products_name 1 products_name A 23202 NULL NULL BTREE
products_description 1 products_description 1 products_description NULL 1 NULL NULL YES FULLTEXT


----------------------------------------

these are some attempts that were made and didn't work.

UPDATE products_to_categories
SET products_to_categories.categories_id =987
FROM products_description
INNER JOIN products_to_categories
ON products_description.products_id = products_to_categories.products_id
WHERE `products_name` LIKE 'ABC%'
(this gave a 1064 syntax error)

UPDATE products_description, products_to_categories
SET products_to_categories.categories_id = 987
WHERE products_description.products_id = products_to_categories.products_id
AND products_description.products_name LIKE 'ABC%'
(gave a 1062 error. duplicate entry)