In my notes table (MySQL database) I have id as the primary key. I want the column originalid to be the value of the primary key. Here is the query I run after creating a record which I thought would work.
UPDATE notes SET originalid = (SELECT MAX(id) FROM notes) where id = (SELECT MAX(id) FROM notes);
But I get the error
You can't specify target table 'notes' for update in FROM clause
Any advice or solutions on how I can work around this?
I explained the error message in an earlier post 4.5 years ago (Problem with MySQL subquery)
SELECT id INTO @maxid FROM notes ORDER BY id DESC LIMIT 1;
UPDATE notes SET originalid = @maxid where id = @maxid;
or
SELECT MAX(id) INTO @maxid FROM notes;
UPDATE notes SET originalid = @maxid where id = @maxid;
MAX(id) can have different values in case you have concurrent users. Even in a transaction you can read different values in a READ-COMMITTED and READ-UNCOMMITTED isolation level.
Just write exactly what you described with limiting the update to a reasonable subset of rows. For example running this after your insert will always set the correct values.
UPDATE notes SET originalid = id WHERE originalid IS NULL;
This will only update originalid which haven't yet been set and with the correct value.
Also please note that you can have the last insert id by the function LAST_INSERT_ID function. https://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
So this would also work (and you don't need an index on originalid to be effective)
UPDATE notes SET originalid = id WHERE id = LAST_INSERT_ID();
Can you not just run:
UPDATE notes SET originalid = id;
Make sure your data types match (e.g. if id is INT(11), originalid should also be INT(11).
