How to do a percentage of a column value, and then insert that value into a column
The table below should be the output that I want to achieve. That means depending on what value is in column1 which is the percentage and what value is in column2. column1 will do a percentage of column2. Like 80% of 1000 = 800, which will be changed in column2 of 2nd record.
Currently my code only can select the current record and insert another copy which will duplicate the same record. But now I need to calculate and overwrite column 2 values depending on the perentage. I didn't put my select code here as I scared you all will get confused. I only put my insert code here, pls tell me how to do the formula in the code for the percentage for column2 thanks.
Orginal 1st record-> column1 column2 80 1000 Inserted copy records which is 2nd record column1 column2 (what I want to achieved) 80 800
INSERT INTO table1 (column1, column2) VALUES (tran.column1, tran.column2);
If you don't mind adding a third column, this can be done quickly without triggers, in Oracle 11 or later, with a function-based, virtual column.
create table table1 (col1 integer, col2 integer, col3 as (col1 / 100 * col2)); SQL> insert into table1(col1,col2) values(80, 1000); 1 row created. SQL> select * from table1; COL1 COL2 COL3 ---------- ---------- ---------- 80 1000 800 SQL>
The advantage is you retain the original value, so if COL1 (percentage) is updated, or you change the formula, the results recalculate.
Otherwise, create a BEFORE INSERT FOR EACH ROW trigger.
CREATE OR REPLACE TRIGGER ins_table1 BEFORE INSERT ON table1 cFOR EACH ROW BEGIN :NEW.col2 := :NEW.col1 / 100 * :NEW.col2; END; /
If your data already exists in the table, you can just update that row:
UPDATE table1 SET column2 = column1 / 100 * column2 WHERE table1.id = ...
I would still recommend @mrjoltcola's solution if you are on a recent DB version and can alter the table. It's far more elegant and allows you to see values which your calculation is based on. This UPDATE statement, if run more than once, will cause you to lose the values of column2.