Update with a subquery

Hi, It would be useful to provide DDL. However, you haven't mentioned the name of the table from where category name to be read, so I have used t1 as the table and column name as place holders.

SELECT p.*  

FROM Products as p
 
WHERE p.UnitsInstock > 50 AND p.CategoryID --//CategoryID:3 'Confetions'
IN (select CategoryID from t1 where Categoryname = 'Confetions'  )
 
 
 
UPDATE Products
 
SET UnitPrice = 10.00
 
WHERE Unitsinstock > 50 AND CategoryID in 
(select CategoryID from t1 where Categoryname = 'Confetions'  )
 
 
 
SELECT *  

FROM Products
 
WHERE UnitsInstock > 50 AND CategoryID in 
(select CategoryID from t1 where Categoryname = 'Confetions'  )

Please take a look and tweak accordingly.