Add all values of specific columns in a row in MySQL and store in another column of same row

Hi,

I have a table named table_1. In which I have columns col_1, col_2, col_3, col_4 and total. I now insert a row with values for col_1, col_2, col_3 and col_4  as Test, 1, Hello and 2 respectively. I want that the sum of col_2 and col_4 is automatically stored in total column of the same row.

If later the values of col_2 or col_4 or both columns is changed then the value in total column should automatically get updated.

Kindly let me know if anything else is required.

Thanks in advance.
LVL 1
Vipin KumarSr. Network EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
You need to use a trigger on insert and on update that will evaluate the two columns and update total.
This will create an unnecessary overhead on the MySQL. Why not get the data when you need it, or use a query that sums the two columns if that is the query criteria.

You can handle the update to total column when you perform the update and the insert.

another option to,periodically run update table_1 set total=col_2+col_4.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter RitzelSenior Software EngineerCommented:
I would agree with @arnold approach, to calculate that automatically when needed (most probably in a report).
But before jump to that, make sure that you dont need that to show up in many screens of an app, for example. In this case, pre-calculate the value based on triggers would be faster.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.