How to create a derived attribute column in sql which is sum of other two existing columns-CodePudding

CREATE TABLE Software
(
    name varchar(8) not null,
    title varchar(20) not null,
    dev_in varchar(8) not null,
    scost decimal(7,2),
    dcost integer(5),
    sold integer(3)
);

I want to have a column selling_price which is sum of scost and dcost.

CodePudding user response：

You can use MySQL generated columns for it. Reference Doc

-- when creating table

create table software ( 
  name varchar(8) not null,
  title varchar(20) not null, 
  dev_in varchar(8) not null, 
  scost decimal(7,2), 
  dcost integer(5), 
  sold integer(3), 
  selling_price decimal(7,2) as (dcost   scost) 
);

-- or for an existing table

alter table software add column selling_price decimal(7,2) as (dcost   scost);

mysql> insert into software (name, title, dev_in, scost, dcost, sold)
    -> values("s", "s", "ss", 100.1, 2, 3);

mysql> select * from software;
 ------ ------- -------- -------- ------- ------ --------------- 
| name | title | dev_in | scost  | dcost | sold | selling_price |
 ------ ------- -------- -------- ------- ------ --------------- 
| s    | s     | ss     | 100.10 |     2 |    3 |       102.10  |
 ------ ------- -------- -------- ------- ------ --------------- 
1 row in set (0.00 sec)