I want to insert data to a column from its own table data?

Question

• I have a table called T1.
• I have to insert data into new column Change of T1's table.
• Change column values will be difference of columns open and close (close-open).

Datetime	open	Close	Change
14-03-2000	1561.55	1567.05
15-03-2000	1546.8	1620.1
16-03-2000	1620.4	1562.2
21-03-2000	1563.3	1556.6

I am trying with below query but its getting error

SQL Error: ORA-00936: missing expression

insert into T1(change) 
values
(
select 
case when open<close then close-open
     when open>close then close-open
end as change
from T1
order by datetime
);

Answer 1

When you want to populate columns of existing rows, you use an update statement, not an insert statement. Simply use an update statement and apply the expression in the set clause:

update t1 set change = close-open;

However, storing derived values is not a good design practice. What happens if someone or some code updates one of the component columns and forgets to also update the derived column? You can easily get inconsistent data this way. It is best to compute derived columns at query time rather than storing them. Simply do so in the select statements you use when you query the original table:

select t1.*,close-open change from t1

If there is a rather consistently used formula then you may want to add a "virtual column" to the table, which will apply the formula for you at query time as if the value were stored in the table, though it isn't - under the covers it is simply computing the formula when you query it:

alter table t1 add (change number as (close-open) virtual);

Now query:

select * from t1

Or create a view that embeds the formula:

create view v1 as select t1.*,close-open change from t1

Now query:

select * from v1

You have lots of options to permit you to get the derived value at query time so you don't have to actually store it in the table and risk data inconsistency. But if you do want to store it physically, use the simple update statement.