CODEWITHSUNDEEP
sqlpostgresql
user2899444
user2899444

Reputation: 323

Updating column in another table with combined columns

So I have two tables: surveys and survey_ids, where I'm essentially migrating some of the data from survey_ids into surveys.

'surveys' has one column called dist_string which should ideally be the combination of the columns 'dist_admin' and 'dist_user' in survey_ids.

I wanted to do an update on surveys.dist_string (which is currently '' for all records) while concurrently merging the two columns survey_ids.dist_admin and survey_ids.dist_user together into surveys.dist_string, but I've been a little stuck. I have other columns in the surveys table with a not null constraint, so I run into problems if I were to try an insert into for dist_string. There's data already in the other columns, inserted from survey_ids.

From survey_ids:

 dist_admin    | dist_user
 --------------+--------------
               | 456dfghjk46
  0987ghasdf7  |
               | 123ghjwe46c
               | 5thw67de523
  4r5thgffghk  | 
  2qrt75434th  | 
               | 876tjheg3wg
  9uh7ygwedf6  |

I want dist_string from surveys to look like

  dist_string   
 -------------
  456dfghjk46   
  0987ghasdf7
  123ghjwe46c
  5thw67de523 
  4r5thgffghk
  2qrt75434th
  876tjheg3wg 
  9uh7ygwedf6

How do I accomplish this?

Edit: @coalesce, when I do that, I get for dist_string:

  dist_string   
 -------------

  0987ghasdf7


  4r5thgffghk
  2qrt75434th

  9uh7ygwedf6

Upvotes: 0

Views: 54

Answers (2)

Houari
Houari

Reputation: 5641

You can use the COALESCE to select the non-null value of the two columns:

select coalesce(dist_admin, dist_user);

So your update should be like:

update surveys set dist_sting = coalesce(NULLIF(dist_admin,'') , NULLIF(dist_user,'') )
 FROM survey_ids  WHERE  surveys.id = survey_ids.id;

Upvotes: 1

Marth
Marth

Reputation: 24812

From 

SELECT * FROM survey_ids;
┌────┬────────────┬───────────┐
│ id │ dist_admin │ dist_user │
├────┼────────────┼───────────┤
│  1 │ (null)     │ ab1       │
│  2 │ cd2        │ (null)    │
└────┴────────────┴───────────┘
(2 rows)

and

SELECT * FROM surveys;          
┌────┬─────────────┐
│ id │ dist_string │
├────┼─────────────┤
│  1 │             │
│  2 │             │
└────┴─────────────┘
(2 rows)

Running

UPDATE surveys
SET dist_string = COALESCE(dist_admin, dist_user)
  FROM survey_ids
  WHERE surveys.id = survey_ids.id
;

Will update surveys to :

SELECT * FROM surveys;
┌────┬─────────────┐
│ id │ dist_string │
├────┼─────────────┤
│  1 │ ab1         │
│  2 │ cd2         │
└────┴─────────────┘
(2 rows)

In case the values are not NULL be the empty string, use :

UPDATE surveys   
SET dist_string = CASE WHEN dist_user = '' THEN dist_admin ELSE dist_user END
  FROM survey_ids
  WHERE surveys.id = survey_ids.id
;

or, mixing both (in case you have empty strings and NULLs) :

UPDATE surveys   
SET dist_string = CASE 
                    WHEN dist_user = '' THEN dist_admin
                    WHEN dist_admin = '' THEN dist_user
                    ELSE COALESCE(dist_admin, dist_user)
                  END
  FROM survey_ids
  WHERE surveys.id = survey_ids.id
;

Upvotes: 0

Related Questions