CODEWITHSUNDEEP
sql
Chaim Shtokhamer
Chaim Shtokhamer

Reputation: 55

add a new column that the values will be a calculation from the same table

I'm trying to add a new column to the table called a summary, that the values it gets will be a calculation of column 1 - column 2 from the same table (summary),

This is the table:

TABLE SUMMARY

id target Sales
******** 10 14

Here's what I wrote -

ALTER TABLE Summary
ADD difference int;
Insert into (difference)
Value (SELECT target – Sales FROM Summary);

Is this a valid query, and if not, how to write it more correctly? Thanks!

Upvotes: 1

Views: 2498

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

The logic that you are trying to implement would seem to be update:

ALTER TABLE Summary ADD difference int;

UPDATE Summary
    SET difference = (target – Sales);

Note that this only works on the data already in the table. The value of difference in new rows or in rows where the data changes is out-of-date -- unless you insert the correct values or do further updates.

Most databases support generated columns. These are a great convenience -- and ensure that the value is always in synch when you query it. Typical syntax is:

alter table summary add difference int generated always as
     (target - Sales);

As written, this value is calculated when the table is queried (which adds a very small overhead). Most databases can also persist generated columns, so the database actually stores the value -- but also keeps it up-to-date.

Upvotes: 2

Emre
Emre

Reputation: 77

Add a calculated column to that table :

alter table summary add diff as  target – Sales

it will be calculated without effort.

note that this is calculated on the fly, IT HAS A COST ON EVERY EXECUTION

Upvotes: 0

Related Questions