I'm working on a project for my database class, and I have a table called "accounts". It looks like this

What I want to do is have a stored procedure where it calculates the new ranks for each account. The ranks will be based off a value (higher the better) that will be computed with an equation I am working on. How would I be able to get the rank, and store the rank in my table?

Ex: Account_id||Games_won||Games_lost||(calculated value) not in table||Account_rank

         1        200           157              1856                    1

EDIT: Here is my CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS league_of_legends.accounts (


account_name VARCHAR(45) NOT NULL,

account_password VARCHAR(45) NOT NULL,

subscriber_first_name VARCHAR(45) NOT NULL,

subscriber_last_name VARCHAR(45) NOT NULL,

subscriber_email_address VARCHAR(45) NOT NULL,

subscriber_dob DATE NOT NULL,

server VARCHAR(45) NOT NULL,

account_balance INT NULL,

games_won INT NULL,

games_lost INT NULL,

account_rank INT NULL,

PRIMARY KEY (account_id, account_name, subscriber_email_address));

My INSERT sample statements: INSERT INTO accounts (account_id, account_name, account_password, subscriber_first_name, subscriber_last_name, subscriber_email_address, subscriber_dob, server, account_balance, games_won, games_lost, account_rank)

VALUES (DEFAULT, 'Bwomp', 'asdfqwer', 'Nile', 'Williams', '[email protected]', '2003-10-09', 'NA', 1250, 100, 100, NULL),

(DEFAULT, 'Dirty Dan', '1234567890', 'Daniel', 'Brown', '[email protected]', '1990-07-16', 'EU', 0, 3, 20, NULL),

(DEFAULT, 'Alfy', 'doodcool', 'Alfonso', 'Riggleman', '[email protected]', '1992-12-27', 'KR', 50, 400, 410, NULL),

(DEFAULT, 'Justaguy1', 'retrodonkey', 'Nick', 'Chetta', '[email protected]', '1996-06-28', 'NA', 3, 0, 5, NULL);

Sample equation:

(Games_won / total_games) * games_won = value

  • Please post a SHOW CREATE TABLE accounts\G. Also, please give us some sample DDL (INSERT INTO accounts VALUES(....)) and your desired result and the logic you used to get it. Also, need a sample ranking equation - 1856/43 = 43.16... how is it calculated?
    – Vérace
    Commented Apr 30, 2015 at 20:17
  • ok, let me edit it.
    – Zach
    Commented Apr 30, 2015 at 20:31
  • Need the equation if you could post that also.
    – Vérace
    Commented Apr 30, 2015 at 20:50
  • Just posted it.
    – Zach
    Commented Apr 30, 2015 at 20:51

2 Answers 2


OK - this should be easy for the first part, if you're willing to be a bit adventurous :-).

MariaDB has had computed columns (otherwise known as virtual or generated columns) for quite a while now. Otherwise, the latest milestone release of MySQL (5.7.7) also has them.

If you want to do this with a current GA version, you'll have to use TRIGGERs.

However, if you want to update the RANK (see a lovely post here) on each update of games_won/games_lost, then you'll have to call a PROCEDURE which will update RANK. MySQL's Stored Procedure programming language isn't sophisticated enough for this on its own, but see here for some ideas. It says that there can be rollback issues, but since you're updating only one table, it should be easier than most - good luck :-). BTW, I'd use float/double for games_won/_lost - otherwise, you might have problems with integer division.

  • Thanks for the information! I'm going to try experimenting with this, and hopefully I get it to work properly. =)
    – Zach
    Commented Apr 30, 2015 at 21:17

My $0.02; perform the calculation app side. It will scale better. Its possible to perform the calculation in an update or an insert on duplicate key update statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.