Update column value where property of foreign key table is equal to property of foreign key table-CodePudding

I am looking to do an update basing myself on the property of another table to which my initial table has a foreign key to, so given the case I have 3 tables:

Product
Id | Name | ProductCategoryId | GameId
1    Prd-A    1                   1
2    Prd-B    1                   1
3    Prd-C    3                   1

Game
Id | Name
1    Game A
2    Game B
3    Game C

ProductCategory
Id | Name
1    Category A
2    Category B
3    Category C

Is there a way for me to do something like

UPDATE Product
SET GameId= (GameTable Where name = 'Game B').Id
WHERE ProductCategoryId= (ProductCategory Where name = 'Category C').Id;

The reason I cannot use the Id even knowing it is that I am dealing with different environments and the Id does not always match between environments, so I need to use another property that doesn't change.

https://www.db-fiddle.com/f/sNP8nZc8TsJNHGGqm8STYY/0 here is a fiddle with the example.

CodePudding user response：

Do you need in this:

UPDATE PRODUCT p
JOIN PRODUCTCATEGORY pc ON p.productcategoryid = pc.id
JOIN PRODUCTCATEGORY pcu ON pcu.name = 'CATEGORY B'
SET p.productcategoryid = pcu.id
WHERE pc.name = 'CATEGORY A';

https://www.db-fiddle.com/f/sNP8nZc8TsJNHGGqm8STYY/1

CodePudding user response：

UPDATE PRODUCT p 
JOIN PRODUCTCATEGORY pc ON pc.name = 'CATEGORY C' 
JOIN GAME g ON g.name = 'GAME B' 
SET p.gameid = g.id 
WHERE p.productcategoryid = pc.id;

This is what I was looking for given the example above, thanks Akina for the help