matt_lists wrote:
matt_lists wrote:
"One restriction is that currently you cannot modify a table and select from the same table in a subquery. This applies to statements such as |DELETE|, |INSERT|, |REPLACE|, and |UPDATE|."
Anyboyd know if this will be fixed?
Working around it with a join on itself, is there some reason updating a table with a subquery on itself is not alowed?
Done it many times with other db's
Ok I was wrong, it wont let me do that
so how does everyone work around this "bug" (at least to me it's a bug)
senario is, id, audit, recno
I need to set audit = F on the record with the max(recno)
in most db's I would do update table set audit = f where recno = (select max(recno) from table)
but mysql does not allow updates on a table using a nested query on itself
so I figured a self join would work, but nope that does not work either
My first thought was
SELECT @max_rec:= MAX(recno) FROM table; UPDATE table SET audit = 'F' WHERE recno = @max_rec;
but you'd probably need to lock the table to prevent someone doing an INSERT between the SELECT and UPDATE.
So this is probably better:
UPDATE table SET audit = 'F' ORDER BY recno DESC LIMIT 1;
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
