1

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_id INT NOT NULL AUTO_INCREMENT,

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', 'getrekt@gmail.com', '2003-10-09', 'NA', 1250, 100, 100, NULL),

(DEFAULT, 'Dirty Dan', '1234567890', 'Daniel', 'Brown', 'awovuca-1084@yopmail.com', '1990-07-16', 'EU', 0, 3, 20, NULL),

(DEFAULT, 'Alfy', 'doodcool', 'Alfonso', 'Riggleman', 'AlfonsoRRiggleman@armyspy.com', '1992-12-27', 'KR', 50, 400, 410, NULL),

(DEFAULT, 'Justaguy1', 'retrodonkey', 'Nick', 'Chetta', 'NicholasSMaine@jourrapide.com', '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 Apr 30 '15 at 20:17
  • ok, let me edit it. – Zach Apr 30 '15 at 20:31
  • Need the equation if you could post that also. – Vérace Apr 30 '15 at 20:50
  • Just posted it. – Zach Apr 30 '15 at 20:51
1

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 Apr 30 '15 at 21:17
-1

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, privacy policy and cookie policy

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