I have four (4) columns in a MySQL table and I want to add three(3) of the column and set the value in the last column, but I don't know the SQL command to use.

I tried,

UPDATE tablename SET total = first_column + second_column + third_column

but it is only updating it as I run the command. I want the values to be SET anytime the records is being added.

Any idea with the right SQL command to use? Thanks.

GetAvenue:
I have four (4) columns in a MySQL table and I want to add three(3) of the column and set the value in the last column, but I don't know the SQL command to use.

I tried,


but it is only updating it as I run the command. I want the values to be SET anytime the records is being added.

Any idea with the right SQL command to use? Thanks.

Hello @GetAvenue,

What you require is a TRIGGER see https://en.wikipedia.org/wiki/Database_trigger. However, MySQL 5.5 which I tested with seems to have a documented issue of locking tables and thus preventing you from using triggers for your scenario. Please see http://forums.mysql.com/read.php?99,122354,122354#msg-122354. Please check the latest version of MySQL for current behaviour.

The trigger would have looked like this:

CREATE TRIGGER my_trigger
AFTER INSERT ON tablename FOR EACH ROW
UPDATE tablename SET total = first_column + second_column + third_column;

If you have the flexibility of using a different RDBMS, I strongly recommend switching to PostgreSQL. It provides comparable features to Oracle and adheres to many ANSI SQL standards.

I hope this helps

I can't switch DBMS