Clever Geek Handbook

Set one row field as multiplication by 2 others

I have a SQL table structure like this

Id    A     B    C   D
1     1     5    6   25
2     2     10   5   25
3     3     7    4   25
4     1     6    5   26
5     2     10   5   26
6     3     8    3   26

I want to write a script that will update all columns B and C in rows with A = 3 with the multiplication value A = 1 and A = 2 (for the same value of column D )

So the result should be

Id ABCD
1 1 5 6 25
2 2 10 5 25
3 3      50    30   25
4 1 6 5 26
5 2 10 5 26
6 3      60    25   26

How can I write code like this in SQL?

+3


source to share


1 answer


One possible way is to concatenate the table into itself twice:



update T3
set
    T3.B = T1.B * T2.B,
    T3.C = T1.C * T2.C
from [Table] T3
    join [Table] T1 on T1.A = 1 and T1.D = T3.D
    join [Table] T2 on T2.A = 2 and T2.D = T3.D
where
    T3.A = 3
+7


source






More articles:


All Articles