what i have
id s_1 s_2 s_3 s_4 s_5 s_6 s_7 s_8
ax 1 0 0 0 0 0 0 0
bx 0 1 0 0 0 0 0 0
cx 0 0 1 0 0 0 0 0
dx 0 0 0 1 0 0 0 0
ex 0 0 0 0 1 0 0 0
fx 0 0 0 0 0 1 0 0
gx 0 0 0 0 0 0 1 0
hx 0 0 0 0 0 0 0 1
I am trying to update my column values as below .
if i have 1,0,0,0,0,0,0,0, as column values from s1 to s8 then it should be updated as 1,0,1,0,1,0,1,0
if i have 0,1,0,0,0,0,0,0 as column values from s1 to s8 then it should be updated as 0,1,1,0,1,0,1,0
if i have 0,0,1,0,0,0,0,0 as column values from s1 to s8 then it should be updated as 0,0,1,0,1,0,1,0
if i have 0,0,0,1,0,0,0,0 as column values from s1 to s8 then it should be updated as 0,0,0,1,1,0,1,0
if i have 0,0,0,0,1,0,0,0, as column values from s1 to s8 then it should be updated as 0,0,0,0,1,0,1,0
if i have 0,0,0,0,0,1,0,0 as column values from s1 to s8 then it should be updated as 0,0,0,0,1,1,1,0
if i have 0,0,0,0,0,0,1,0, as column values from s1 to s8 then it should be updated as 0,0,0,0,0,0,1,0
if i have 0,0,0,0,0,0,0,1 as column values from s1 to s8 then it should be updated as 0,0,0,0,0,0,0,1
Output looks like below
id s_1 s_2 s_3 s_4 s_5 s_6 s_7 s_8
ax 1 0 1 0 1 0 1 0
bx 0 1 1 0 1 0 1 0
cx 0 0 1 0 1 0 1 0
dx 0 0 0 1 1 0 1 0
ex 0 0 0 0 1 0 1 0
fx 0 0 0 0 1 1 1 0
gx 0 0 0 0 0 0 1 0
hx 0 0 0 0 0 0 0 1
UPDATE table_name AS o
SET (s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8) = (n_1, n_2, n_3, n_4, n_5, n_6, n_7, n_8)
FROM (
VALUES
(1,0,0,0,0,0,0,0, 1,0,1,0,1,0,1,0),
(0,1,0,0,0,0,0,0, 0,1,1,0,1,0,1,0),
(0,0,1,0,0,0,0,0, 0,0,1,0,1,0,1,0),
(0,0,0,1,0,0,0,0, 0,0,0,1,1,0,1,0),
(0,0,0,0,1,0,0,0, 0,0,0,0,1,0,1,0),
(0,0,0,0,0,1,0,0, 0,0,0,0,1,1,1,0),
(0,0,0,0,0,0,1,0, 0,0,0,0,0,0,1,0),
(0,0,0,0,0,0,0,1, 0,0,0,0,0,0,0,1)
) as n (
s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8,
n_1, n_2, n_3, n_4, n_5, n_6, n_7, n_8
)
WHERE (o.s_1, o.s_2, o.s_3, o.s_4, o.s_5, o.s_6, o.s_7, o.s_8) = (n.s_1, n.s_2, n.s_3, n.s_4, n.s_5, n.s_6, n.s_7, n.s_8)