If i have table named shipments with columns origin_state and destination_state. I want a new column named st_st with the value origin_state _ destination_state.
For example, if a row has SC as origin and GA as a destination. I should get SC_GA in the st_st
I tried
INSERT INTO shipments(ST_ST)
SELECT CONCAT(`Origin_state` , '_' ,`Destination State`) AS ST_ST
From shipments;
this query starts to insert the values at the end of the table not to the corresponding row. Please help.
Use a generated column:
alter table shipments add column st_st varchar(255) generated always as
concat(`Origin_state` , '_' ,`Destination State`) ;
You can also change the values using update:
update shipments
set st_st = concat(`Origin_state` , '_' ,`Destination State`);
However, a generated column ensures that the value in st_st is always consistent, even for new rows and if the values change in existing rows.