CODEWITHSUNDEEP
mysqlsql
user5749981
user5749981

Reputation:

Multiply by percentage

I have a table in my database with 2 rows, Level, and Experience.

CREATE TABLE `player_xp_for_level` (

    `Level` TINYINT(3) UNSIGNED NOT NULL,
    `Experience` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`Level`)
)

COLLATE='utf8_general_ci'

ENGINE=MyISAM
;

The level experience up to level 80 have been filled in by a predefined index. However I would like the stats for level 81 to be based on the experience of level 80 but just multiplied by *1.0115

Basically I'm looking for a query that inserts one row at a time. Checks the previous Experience, and then modifies it and inserts it. Workflow:: Checks previous row, updates values (previous experience*1.0115) and inserts.

Upvotes: 1

Views: 2202

Answers (3)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

If you want only to show higher score without affecting data in the database. You could use CASE expression in the SELECT statement:

SELECT player, lvl,
    CASE 
        WHEN lvl BETWEEN 80 AND 255 THEN score * 1.0115 
        ELSE score 
    END as score
FROM player_xp_for_level

As you have posted additional info, I've updated my answer with the INSERT statement. There also you could use CASE expression in following:

INSERT INTO player_xp_for_level (lvl, score) 
                        VALUES (@lvl, CASE WHEN @lvl BETWEEN 80 AND 255 THEN @score * 1.0115 ELSE @score END);

Upvotes: 2

Andrew
Andrew

Reputation: 7880

From the minimum code you provided, I think this is what you want:

UPDATE player_xp_for_level
SET name_of_value_column = name_of_value_column * 1.0115
WHERE name_of_level_column BETWEEN 80 AND 255;

Upvotes: 0

Richard Hansell
Richard Hansell

Reputation: 5403

Assuming the structure of your table, which isn't clear from the question, then something like this?

UPDATE
    player_xp_for_level
SET
    xp = xp * 1.0115
WHERE
    player_level BETWEEN 80 AND 255;

Upvotes: 0

Related Questions