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

My codes

INSERT INTO table1 (column1, column2) VALUES (tran.column1, tran.column2);

Answers

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.

Need Your Help