>I have a SQL like this which gives me a result. The last two fields are empty because I just created them. The goal is to update the result in those two fields with the relational value found:
>
>
>SELECT Color.Title_E,Color2.Title_E AS Title_E2,Color,Color2 FROM RepairOrder
> LEFT JOIN Color ON RepairOrder.NoColor=Color.Numero
> LEFT JOIN Color AS Color2 ON RepairOrder.NoColor2=Color2.Numero
>
>
>So, basically, the first two fields contains the relational value from the color table. The Color and Color2 fields have been added in the RepairOrder table and they are Character(40). How can I use this SQL and turn it into an update to update the Color and Color2 fields with the relational value found in the first two fields?
>
>The reason is that we are dropping the color table as there wasn't any use to keep it. So, we will save the value as is in the character field from now on. But, we need to update the actual records. I thought I would use an approach like that.
Try
;with cte as (SELECT Color.Title_E,Color2.Title_E AS Title_E2,Color,Color2 FROM RepairOrder
LEFT JOIN Color ON RepairOrder.NoColor=Color.Numero
LEFT JOIN Color AS Color2 ON RepairOrder.NoColor2=Color2.Numero)
update cte set Color = Title_E, Color2 = Title_E2
If it's not broken, fix it until it is.
My Blog