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’
Current output:
ItemPath Name Attribute Color Lane1 LDSSYSSA0022960 SetupType Green Lane7 OBS NLDSSYSSA0 Color NULL Lane3 OLD2768 Gap NULL Lane7 NLDSSYSSA0022L SetupType Red Lane7 NLDSSYSSA0022LDSS Size Red Lane1 LDSSYSSA00221254 SetupType Red Lane1 LDSSYSSA00221255 ID Red Lane1 LDSSYSSA00221351 SetupType Yellow Lane1 LDSSYSSA00221211 Size Yellow
The desire output: The changes should also be reflected in the original tables before I joined all table together.
ItemPath Name Attribute Color Lane1 LDSSYSSA0022960 Normal 1 Lane7 OBS NLDSSYSSA0 Color NULL Lane3 OLD2768 Gap NULL Lane7 NLDSSYSSA0022L SetupType Red Lane7 NLDSSYSSA0022LDSS Size Red Lane1 LDSSYSSA00221254 SetupType Red Lane1 LDSSYSSA00221255 ID Red Lane1 LDSSYSSA00221351 Normal 1 Lane1 LDSSYSSA00221211 Size Yellow
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')
