CODEWITHSUNDEEP
sqlmysql
tipu
tipu

Reputation: 9604

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 = select max(num) from table;

update table set num = num / val

Upvotes: 1

Views: 122

Answers (2)

Unreason
Unreason

Reputation: 12704

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.

Upvotes: 0

Jordan Running
Jordan Running

Reputation: 106077

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;

Upvotes: 2

Related Questions