I’ve been having a week where there’s a ton of stuff to be done in a short time, yet everything seems to take much longer than it should and every task seems to present multiple unforseen obstacles.
Yesterday, for example, I was working on something that had already taken several days of my time when I’d hoped to get it done in hours. The final straw came when I found myself facing what appeared to be an intractable problem: a process that needed to insert multiple rows in a table, and increment a certain column in each row to get a unique value. I couldn’t use an auto-increment value in this case (there was one already), and using a separate query caused concurrency problems (resulting in non-unique values). I should just point out that this is a system that I’ve inherited, not one that I developed myself.
I started out with a simple update, like this:
UPDATE my_table SET some_value = ( MAX( some_value ) + 1 ) WHERE id = 123;
…which in hindsight was never going to work but anyway, it gave me this error:
#1111 - Invalid use of group function
So I thought about it a bit and then struck on this:
UPDATE my_table SET some_value =
SELECT MAX( some_value )
WHERE id = 123
…which gave me this error:
#1093 - You can't specify target table 'my_table' for update in FROM clause
…damn. I was starting to bang my head on the desk at this point, but Google came to my rescue by pointing me at this thread, which suggested using a nested sub-select. The result was the following rather ugly query:
SELECT MAX( my_value ) AS selected_value
WHERE id = 123
…which works. It’s not great from a performance point of view since it uses a temporary table, but it does what it needs to. I hope in future that I will be given time to go back and rewrite this part of the system so as to entirely remove the issue.