1

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?

3 Answers 3

1

I explained the error message in an earlier post 4.5 years ago (Problem with MySQL subquery)

SUGGESITON : Do it as two queries

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;
0

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).

2
  • That would overwrite original_id where the primary key id on every row. The question shows that Schwarz only wants this to occur on the last row. Commented Sep 14, 2015 at 16:35
  • Okay. There was no indication in his question as to why he was applying the MAX() in his SQL statement. It just appeared that he was overcomplicating the query based on the wording of his question. Commented Sep 14, 2015 at 16:39
0

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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.