I'm trying to get the number of rows in a table or column and place that value inside an equation, like this:
UPDATE myTable
SET myCalculatedColumn = COUNT(*) / (@rownum:= 1 + @rownum)
WHERE 0 = (@rownum:=0)
Unfortunately, I get an error 1111 "Invalid use of group function". I've also tried:
SET @c = COUNT(*);
UPDATE myTable
SET myCalculatedColumn = @c / (@rownum:= 1 + @rownum)
WHERE 0 = (@rownum:=0)
But this produces the same error.
How can I place COUNT(*) (or a programmatically equivalent operation) into an equation?
Join with a subquery that gets the count. You can also initialize the @rownum variable there as well.
UPDATE myTable AS t
CROSS JOIN (SELECT COUNT(*) AS count, @rownum := 0 FROM myTable) AS c
SET myCalculatedColumn = count / (@rownum := 1 + @rownum)
If you don't want to do a cross join, you can use the subquery when setting @c. You just have to tell COUNT(*) what table to count from.
SET @c = (SELECT COUNT(*) FROM myTable);
SET @rownum = 0;
UPDATE myTable
SET myCalculatedColumn = @c / (@rownum:= 1 + @rownum);
Note that the order that it assigns to myCalculatedColumn will be arbitrary and unpredictable unless you also have an ORDER BY clause.