MySQL / MariaDB place COUNT(*) in equation

Question

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?

Answer 1

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.