changing a column based on it's highest value

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 = se开发者_StackOverflow社区lect max(num) from table;

update table set num = num / val

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;

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.