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

Unbind model from view in Backbone.js

javascript jquery-mobile backbone.js

I have an audio player class which controls the HTML5 audio player. In that I am monitoring the the audio player events and triggering them to the associated view. On the view file this is how I bi...

using cPickle to create savestate for pause

python event-handling pygame pickle

I've been working on a small 2d top-down shooter in python using mostly the pygame lib.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.