Skip navigation links

MySQL Forums :: Newbie :: need help creating a mysql 5.0.95 query


Advanced Search

need help creating a mysql 5.0.95 query
Posted by: jae ahn ()
Date: March 22, 2012 01:13PM

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)

Options: ReplyQuote


Subject Written By Posted
need help creating a mysql 5.0.95 query jae ahn 03/22/2012 01:13PM
Re: need help creating a mysql 5.0.95 query Rick James 03/24/2012 10:31AM
Re: need help creating a mysql 5.0.95 query jae ahn 03/24/2012 10:39AM
Re: need help creating a mysql 5.0.95 query Rick James 03/25/2012 11:57PM
Re: need help creating a mysql 5.0.95 query jae ahn 04/04/2012 12:51PM
Re: need help creating a mysql 5.0.95 query Rick James 04/06/2012 09:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.