I'm working on a project for my database class, and I have a table called "accounts". It looks like
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
Best Answer
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.