1

I have two tables - ProductCategories and Products:

ProductCategories
---
productid    categoryid
1            
2
3
4


Products
--------
productid    productname
1            Apple
2            Apricot
3            Tomato
4            Watermelon

and I would like to populate categoryid in table ProductCategories in such manner that all products having names starting with "Ap" are set to categoryid 1, starting with "To" to category 2 and starting with "Wa" to category 3 resulting with table ProductCategories populated as follows

ProductCategories
---
productid    categoryid
1            1
2            1
3            2
4            3

I have tried this

UPDATE ProductCategories 
SET categoryid = '1' 
WHERE product_id IN 
      (SELECT product_id 
       FROM Products 
       WHERE productname LIKE 'Ap%')

but query does nothing. I would have run modified query few times for other options ("To", "Wa", etc) to completely populate ProductCategories table.

0

Please try, if you could, this approach:

UPDATE 
    ProductCategories, 
    Products
SET 
    ProductCategories.categoryid = CASE LEFT(UCASE(LTRIM(Products.productname)), 2)
                                      WHEN 'AP' THEN 1
                                      WHEN 'TO' THEN 2
                                      WHEN 'WA' THEN 3
                                      ELSE ProductCategories.categoryid
                                   END
    WHERE 
        ProductCategories.product_id = Products.product_id

I assumed that product ID column is named product_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.