Make another column in a table equal to the primary key column

Question

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?

score 1 · Accepted Answer · edited Jun 15 '20 at 09:05

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;

edited Jun 15 '20 at 09:05

Community

1

answered Sep 14 '15 at 15:56

RolandoMySQLDBA

182,700
33
317
520

score 0 · Answer 2 · answered Sep 14 '15 at 15:57

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

answered Sep 14 '15 at 15:57

Nigel Tufnel

101
1

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. – RolandoMySQLDBA Sep 14 '15 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. – Nigel Tufnel Sep 14 '15 at 16:39

score 0 · Answer 3 · answered Sep 15 '15 at 15:10

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

Make another column in a table equal to the primary key column

3 Answers3

SUGGESITON : Do it as two queries