changing a column based on it's highest value

Question

I have a column, num, and I'm trying to update the column such that num = num / MAX(num). Now the thing is that MAX(num) should remain static throughout the update.. it should be the MAX prior to the update. I was wondering whether this was possible to do in one SQL statement. The obvious solution otherwise that I'm currently using it

val = select max(num) from table;

update table set num = num / val

Answer 1

I'm not certain of the syntax, or what the performance implications would be, but how about something like this?

UPDATE table
JOIN (SELECT MAX(num) AS val FROM table)
SET num = num / val;

Answer 2

Keep in mind that

SET @val = (SELECT MAX(num) FROM table);
UPDATE table SET num = num / @val;

most interfaces will allow you to execute the above as one statement.

At the same time, the expected

UPDATE table
SET num = num / (SELECT MAX(num) FROM table);

does not work as you can't have a subquery on the same table you are trying to update in mysql.

However

UPDATE table
SET num = num / (SELECT val FROM (SELECT MAX(num) AS val FROM table) tmp);

creates a temp table out of subquery and bypass the limitation.