Switching Enum Values in SQL-CodePudding

i created an enun in sql

CREATE TYPE asset_type AS ENUM (
     'bat',
     'ball'
);

I want switch the value bat to ball

'ball'
'bat'

how can we switch enum values in sql

CodePudding user response：

ALTER TYPE asset_type RENAME VALUE 'bat' TO 'scratch';
ALTER TYPE asset_type RENAME VALUE 'ball' TO 'bat';
ALTER TYPE asset_type RENAME VALUE 'scratch' TO 'ball';

Not sure what the point is though.

CodePudding user response：

Are you looking to do something like

UPDATE table_name SET column_name = 
  CASE
    WHEN column_name = 'bat' THEN 'ball'
    WHEN column_name = 'ball' THEN 'bat'
  END

probably with a condition, for example

WHERE player_name = 'Jack'

CodePudding user response：

Here we use an assets table and a foreign key so that only valid values from the assets table can be entered in the column.
The proposed update method will not work if there are more than 2 values for the class in assets, but could be modified to use numbers for the assets and then do something like ID = CASE (ID = max ID) then ID = 1 ELSE ID 1

create table assets(
  class varchar(10),
  vall varchar(10),
  constraint pk_enums primary key (class,vall));
insert into assets values
('enum1','bat'),
('enum1','ball');

create table sample (
  clas varchar(10),
  val varchar(10),
  foreign key (clas,val) references assets(class,vall)
  );
insert into sample values('enum1','bat');

select * from sample;

clas  | val
:---- | :--
enum1 | bat

update sample set val = vall 
from assets 
where class = class 
  and vall <> val;

select * from sample;

clas  | val 
:---- | :---
enum1 | ball

db<>fiddle here