CODEWITHSUNDEEP
mysql
W. Yan
W. Yan

Reputation: 59

add a column with data to table in mysql

Now that I want to add a new column with the data already to an existing table in SQL, what should I do.

Say I have a table temp with column (id, price, quantity) with data look like this:

id   price   quantity
01     3     4
02     8     2
03     9     1

and now I want to a derived column call revenue using the code:

select id, price * quantity as revenue
from temp;

the result should look like this:

id   revenue
01     12
02     16
03     9

Now I want to add this revenue into the temp table with existing data here

I have tried alter table with 'as' but it doesn't work, and also I have tried to create a new table with revenue, create a new column in the table then using 'update' will give me something like this:

 id   price   quantity revenue
 01     3     4
 02     8     2
 03     9     1
 null  null null        12
 null  null null        16
 null  null null        9

what should I do then?

Upvotes: 1

Views: 1723

Answers (2)

dossy
dossy

Reputation: 1678

To answer your question:

ALTER TABLE temp ADD COLUMN revenue INT AFTER quantity;
UPDATE temp SET revenue = price * quantity;

This will add the new column, then update every row's revenue column value with the computed price * quantity of each row.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

I recommend not actually adding a new column to your table for the revenue, since it is just derived from other columns. Instead, consider creating a view:

CREATE VIEW temp_revenue AS
SELECT id, price * quantity AS revenue
FROM temp;

If you are using the NDB storage engine, then you could also take advantage of computed columns, e.g.

CREATE TABLE temp (
    id INT AUTO_INCREMENT,
    price DECIMAL(13,2) NOT NULL,
    quantity INT NOT NULL,
    revenue DECIMAL(13, 2) AS price * quantity,
    PRIMARY KEY (id)
);

But, this would not be an option for you if you are using MyISAM or InnoDB.

Upvotes: 1

Related Questions