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
CodePudding user response:
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 ) ;
CodePudding user response:
You can write your query like the following.
UPDATE v
SET v.vendor_type = (CASE
WHEN client_id IS NULL
THEN INTERNAL
WHEN coach_id <> NULL
THEN VENDOR_COACH
WHEN exm_vendor = 1
THEN EXM
ELSE EXTERNAL
END)
FROM vendor v
CodePudding user response:
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/.