Using Subselect Queries in MySQL

« View Messages as PlainText by Default in Mail.app | Home | Stop Bandwidth Theives - Lighttpd Style »

10/7/2005

Using Subselect Queries in MySQL

As of version 4.1, MySQL supports a more ANSI compliant kit of subqueries. In my case here, allowing a subquery in an IN() clause. MySQL's support for subqueries has historically been non compliant on the SQL standard. Their documentation instead presented ways to reword queries to remove subselects.

In my case, I needed to move hundreds of products in a ZenCart database from one category to another based on common words in the product name. I worked out something like:

UPDATE products_to_categories SET categories_id =CAT_ID_1 WHERE products_id IN (
SELECT d.products_id
FROM products_description d, products_to_categories_temp c
WHERE products_name LIKE '%<em>SEARCHWORDS</em>%'
AND c.categories_id =CAT_ID_2
)

Where CAT_ID_1 is the category to move the products to, CAT_ID_2 is the category they are currently in and SEARCHWORDS is the common word/phrase in the name of the products to be moved. A creative use of space can help in weeding out unintended selections. Use '% ring%' instead of '%ring%' to prevent selecting a product name with 'bring' in it when what you really are trying to move a bunch or rings (ie. the jewelry type).

Popularity: 32%