Update Values of Related Records in Different Tables

I have four different tables that are joint together. What I am struggling with is how to update (or change) the value for two different columns: Attribute & Color. Keep in mind, these two items are located in two different tables before I joined them with the appropriate UUID.
The update (or the change of the values) should take a place only when the Attribute = ‘SetupType’ and the Color = ‘Green’ or ‘Yellow’
I would like to change these two values to:
Attribute = ‘Normal’ and the
Color = ‘1’

Here is the code I have so far:

SELECT REC_ProdItem.ItemPath, REC_Recipe.Name, REC_ProdItemValue.Name AS Attribute, REC_RecipeValue.Value AS Color
REC_RecipeValue ON REC_Recipe.RecipeUUID = REC_RecipeValue.RecipeUUID INNER JOIN
REC_ProdItem ON REC_RecipeValue.ProdItemUUID = REC_ProdItem.ProdItemUUID INNER JOIN
REC_ProdItemValue ON REC_ProdItem.ProdItemUUID = REC_ProdItemValue.ProdItemUUID AND
REC_RecipeValue.ProdItemValueUUID = REC_ProdItemValue.ProdItemValueUUID
WHERE (REC_ProdItemValue.Name = 'SetupType')

Any help on how to change these two values to the original table would greatly be appreciated.

Something like:

select b.recipeuuid
  into #rectemp
  from rec_recipe as a
       inner join rec_recipevalue as b
               on b.recipeuuid=a.recipeuuid
              and b.[value] in ('Green','Yellow')
       inner join rec_proditem as c
               on c.proditemuuid=b.proditemuuid
       inner join rec_proditemvalue as d
               on d.proditemuuid=c.proditemuuid
              and d.proditemvalueuuid=b.proditemvalueuuid
              and d.[name]='SetupType'

update a
   set a.[value]='1'
  from rec_recipevalue as a
       inner join #rectemp as b
               on b.recipeuuid=a.recipeuuid
              and b.proditemuuid=a.proditemuuid
              and b.proditemvalueuuid=a.proditemvalueuuid
 where a.[value] in ('Green','Yellow')

update a
   set a.[name]='Normal'
  from rec_proditemvalue as a
       inner join #rectemp as b
               on b.proditemuuid=a.proditemuuid
              and b.proditemvalueuuid=a.proditemvalueuuid
 where a.[name]='SetupType'

drop table #rectemp;

Thank you bitsmed - I am new to SQL and trying to make sense of your code. Could you help me understand it :disappointed_relieved:a little more? I really appreciated it. I spent many hours trying to solve this issue...

As you cannot update two tables in one query, you have to split it up into two updates.

The first query, finds the nessasary id's to update and saves them to a temporary table. This is actually similar to the query you provided, except for the "into" section.

The second query updates the rec_recipevalue table according to the id's found in the first query.

Last query updates the rec_proditemvalue table according to the id's found in the first query.

Thank you bitsmed for your help.

Another question related: Instead of updating the records in the two tables - Can I just insert the updated records with the new information and leave the previous records without making any changes on them?
How would you accomplish this?

You'd need to find out, how the id field generates next value for tables rec_recipevalue and rec_proditemvalue, and then do an insert for each table - much like the update I showed you.