0

I have a query running on a large table, so I need it to run as optimized as possible, and would like to clean up this particular equation.

SET points = IF( 
    (20 - ABS(pick.guessed_rank - team.rank)) < 0,
    0,
    (20 - ABS(pick.guessed_rank - team.rank))
)

How can i improve this to save the ABS(pick.guess_rank - team.rank) to a variable so my update can look more something like:

@diff = ABS(pick.guess_rank - team.rank;
SET points = IF(@diff < 0, 0, @diff);

Or perhaps there is a better solution than using an if statement for this, but an example of how to use a variable in this situation would still be helpful.

Gisheri
  • 1,645
  • 2
  • 18
  • 27
  • You can do a simple function MYSQL and give too parameter guess_rank and rank and under the function you can ABS en return de result as @diff store in your example or do a UPDATE ON SELECT. https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server https://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when –  Sep 25 '17 at 17:49

1 Answers1

0

You want the GREATEST function that does logically the same thing:

SET @points = GREATEST(20 - ABS(pick.guessed_rank - team.rank), 0);

or more specifically

SET @diff = 20 - ABS(pick.guessed_rank - team.rank);
SET @points = GREATEST(@diff,0);

and for extra bonus points

SET @min_margin=20;
SET @diff = ABS(pick.guessed_rank - team.rank);
SET @points_diff = GREATEST(@diff,@min_margin);

--- EDIT ---

In direct SQL CLI, I had to use @ for every variable. I believe there is a difference in a stored procedure

Jacques Amar
  • 1,803
  • 1
  • 10
  • 12
  • Oh yah, that's what I'm talking about! – Gisheri Sep 25 '17 at 18:18
  • Actually I don't know if this is right. It's throwing an error on the set @diff. Can you use this syntax to set a local variable in an update? I am using this set in an update `update pick SET set @diff = ... SET pick.points = ...` Should it still work? – Gisheri Sep 25 '17 at 18:28
  • I always get in trouble with the use of @ in variable and `SET`. Let me confirm and update the result. The `GREATEST` is still your ticket – Jacques Amar Sep 25 '17 at 18:35