Update records that match like regex from another table 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.
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