MySQL Update – Make Another Column Equal to Primary Key

MySQLupdate

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?

Best Answer

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;

SELECT MAX(id) INTO @maxid FROM notes;
UPDATE notes SET originalid = @maxid where id = @maxid;

Related Solutions

Mysql – Getting duplicate primary key for auto increment column

I reached the max possible value of an int column. Problem resolved by altering table and setting big int than int.

The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215

Reference: http://help.scibit.com/mascon/masconMySQL_Field_Types.html

MySQL – Problem with Nested UPDATE Queries

Try

UPDATE Tab1 JOIN Tab2 ON Tab1.b = Tab2.b
SET Tab1.a = '3'
WHERE Tab1.c = '4'
;

Check the documentation for further information (look for 'multiple-table syntax'). An important point is:

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly. See Section 14.3.5.4, “FOREIGN KEY Constraints”.

You may check a similar example on SQLFiddle.

Best Answer

SUGGESITON : Do it as two queries

Related Solutions

Mysql – Getting duplicate primary key for auto increment column

MySQL – Problem with Nested UPDATE Queries

Related Question