question

samn265 avatar image
samn265 asked

Update Values of Related Records Located 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’

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')		
sqlsql servertable-valued
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please post the DDL of your four tables. From what you've said they sound like they're all normalised you just need to update two of them, not all four as your first paragraph suggests.

0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.