Update Values of Related Records in Different Tables

Transact-SQL
,
1

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’

QueryDesignWCode
QueryDesignWCode.jpg1015×630 185 KB

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
FROM REC_Recipe INNER JOIN
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.
Thanks…

2

Something like:

select b.recipeuuid
      ,b.proditemuuid
      ,b.proditemvalueuuid
  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;
3

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...

4

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.

5

Thank you bitsmed for your help.

6

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?

7

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.