CODEWITHSUNDEEP
sqlpostgresql
pmiranda
pmiranda

Reputation: 8420

Postgresql, insert value from a column

Right now, I have this table:

id|name|status (varchar)|fk_id_test (int)
-----------------------------------------
1 |ABC | X              |
2 | DX | Y              |
3 | ZZ | X              |
4 | TU | Y              |
5 | EE | Z              |

I have to insert in fk_id_test values coming from status column.

The algorithm would be something like:

switch(status)
    case 'X': `fk_ik_test` = 1
    case 'Y': `fk_ik_test` = 2
    case 'Z': `fk_ik_test` = 3
etc.

The result should be this:

Right now, I have this table:

id|name|status (varchar)|fk_id_test (int)
-----------------------------------------
1 |ABC | X              |1
2 | DX | Y              |2
3 | ZZ | X              |1
4 | TU | Y              |2
5 | EE | Z              |3

I have 5 status. This is because I will use another table (test) with the names of those status (varchar).

It's possible to do this in postgresql?

Upvotes: 1

Views: 46

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

One method uses an update with a join:

update t
    set fk_id_test = v.fk_id_test
    from (values ('X', 1), ('Y', 2), ('Z', 3)) v(status, fk_id_test)
    where v.status = t.status;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

do you need update

 update table_name
 set fk_id_test= case status when 'X' then 1
    when 'Y' then 2
    when 'Z' then 3 end

Upvotes: 1

jarlh
jarlh

Reputation: 44696

Are you looking for a case expression? 

case status
    when 'X' then 1
    when 'Y' then 2
    when 'Z' then 3
etc.

end as fk_ik_test

Upvotes: 0

Related Questions