I need to update the column (vendor_type) in the table depending on values in other columns.
Table name = "vendor"
Columns = "client_id"(varchar), "coach_id"(varchar), "exm_vendor"(boolean), "vendor_type"(varchar)
And this is what I want to do with postgresql:
if (client_id != null)
vendor_type = INTERNAL
else if (coach_id != null)
vendor_type = VENDOR_COACH
else if (exm_vendor == true)
vendor_type = EXM
else
vendor_type = EXTERNAL
Postgresql supports a number of ways to express conditional values, but perhaps the closest to your example is the CASE..WHEN expression
https://www.postgresql.org/docs/14/functions-conditional.html
you can put this into an update statement like this
UPDATE vendor
SET vendor_type = ( CASE
WHEN client_id IS NOT NULL
THEN 'INTERNAL'
WHEN coach_id IS NOT NULL
THEN 'VENDOR_COACH'
WHEN exm_vendor
THEN 'EXM'
ELSE
'EXTERNAL'
END ) ;
Postgres have case...when to use with your condition. This is example script:
update vendor
set vendor_type = (case when client_id is not null then INTERNAL
when coach_id is not null then VENDOR_COACH
when exm_vendor = true then EXM
else EXTERNAL
end);
You can readmore at https://www.postgresqltutorial.com/postgresql-case/.
