Can someone tell me the correct syntax for this query:

your syntax would work if you weren't trying to reference the cat2 table... i.e. with that syntax you can just reference fields in the products table using the WHERE clause.

try:
UPDATE `products`,`cat2`
SET products.cat=cat2.cat_desc
WHERE cat2.id = products.cat

http://dev.mysql.com/doc/mysql/en/UPDATE.html