Copilot has shown me a neat trick with MySQL to load incrementing values into a set of fields, like this:
UPDATE table set field = (@n := @n + 1)
where (@n := 0) = 0
which will put 1 into the field in the first row, 2 in the second row, 3 in the third and so on. I'm trying to execute this through NPoco, and thus code it with the at-sign doubled up:
db.Execute(@"UPDATE table set field = (@@n := @@n + 1)
where (@@n := 0) = 0");
however, the Execute throws an exception that "Parameter @n must be defined".
Is there way for me to execute this without interpretation of the @ sign?
PS: I realize that having user variables within expressions is deprecated. Maybe there's a better way to get the same result without user variables?
Here's a solution I tested:
with cte as ( select id, row_number() over () as rownum from mytable )
update mytable join cte using (id) set field = rownum;
This requires MySQL 8.0+ for use of CTE and window function. Prior versions of MySQL are no longer supported.