update column values by other column values in postgresql

Question

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

Don't add the postgresql tag if you are using Greenplum

user330315
– user330315

2018-07-06 05:35:28 +00:00
Commented Jul 6, 2018 at 5:35 — user330315
– user330315, Commented Jul 6, 2018 at 5:35

Roman Tkachuk · Accepted Answer · 2018-07-08 19:37:09Z

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)

answered Jul 8, 2018 at 19:37

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

The Impaler · Accepted Answer · 2018-07-06 01:56:57Z

0

I'm sure there's a single SQL update solution out there, but the following multi-statement is a lot easier to write (and to debug):

update my_table -- case 1: 1,0,0,0,0,0,0,0
  set s1 = 1, s2 = 0, s3 = 1, s4 = 0, s5 = 1, s6 = 0, s7 = 1, s8 = 0
  where s1 = 1 and s2 = 0 and s3 = 0 and s4 = 0 
    and s5 = 0 and s6 = 0 and s7 = 0 and s8 = 0;

Then, for case 2:

update my_table -- case 2: 0,1,0,0,0,0,0,0
  set s1 = 0, s2 = 1, s3 = 1, s4 = 0, s5 = 1, s6 = 0, s7 = 1, s8 = 0
  where s1 = 0 and s2 = 1 and s3 = 0 and s4 = 0 
    and s5 = 0 and s6 = 0 and s7 = 0 and s8 = 0;

You can write the rest of the cases (3, 4, 5, 6, 7, and 8).

answered Jul 6, 2018 at 1:56

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

2 Comments

user8545255 Over a year ago

Yeah.I am trying for single SQL update solution instead of writing multi Sql statements.

The Impaler Over a year ago

It's totally possible to do that. It reminds me of digital circuits when I needed to add two 8-bit integers, and use the least number of transistors to do it (optimal solution). If you really are into it, you'll need to develop the full list of truth tables (en.wikipedia.org/wiki/Truth_table) for each one of the eight bit you have. If you spend a couple of hours, I would love to see your optimal solution.

Collectives™ on Stack Overflow

update column values by other column values in postgresql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related