'PRIMARY' Error where there isn't any duplicated PRIMARY Key-CodePudding

I was using this code

CREATE TABLE TEMP_TABLE AS SELECT * FROM LOG_COLUMN_INFO WHERE STID = 0 AND NAME = 'Comments';
UPDATE TEMP_TABLE SET STID = (SELECT @SID);
UPDATE TEMP_TABLE SET COLUMNID = (SELECT MAX(COLUMNID) 1 FROM LOG_COLUMN_INFO);
INSERT INTO LOG_COLUMN_INFO SELECT * FROM TEMP_TABLE WHERE NOT EXISTS (SELECT * FROM 
LOG_COLUMN_INFO WHERE STID = (SELECT @SID) AND NAME = 'Comments' LIMIT 1);
DROP TABLE TEMP_TABLE;

And it gives me

1062 - Duplicate entry '766' for key 'PRIMARY'

I know that COLUMNID is set as PRIMARY so I made it not to be duplicated. And actually there isn't any value of 766 in COLUMNID in exsisting table.

Also, when I use the same code only changed NAME like this

CREATE TABLE TEMP_TABLE AS SELECT * FROM LOG_COLUMN_INFO WHERE STID = 0 AND NAME = 'Result';
UPDATE TEMP_TABLE SET STID = (SELECT @SID);
UPDATE TEMP_TABLE SET COLUMNID = (SELECT MAX(COLUMNID) 1 FROM LOG_COLUMN_INFO);
INSERT INTO LOG_COLUMN_INFO SELECT * FROM TEMP_TABLE WHERE NOT EXISTS (SELECT * FROM 
LOG_COLUMN_INFO WHERE STID = (SELECT @SID) AND NAME = 'Result' LIMIT 1);
DROP TABLE TEMP_TABLE;

Then it worked. And if I do NAME = Comments again, it gives the error with just changing the value from 766 to increased one.

Anyone has any assumption why this is happening?

CodePudding user response：

This:

UPDATE TEMP_TABLE SET COLUMNID = (SELECT MAX(COLUMNID) 1 FROM LOG_COLUMN_INFO);

sets all the rows to have the same value. So your insert to LOG_COLUMN_INFO will fail on the second row.

I suggest you explicitly create the temp table and make an autoincrementing primary key starting with 1 (and then only select all the other columns from LOG_COLUMN_INFO when you do your insert into TEMP_TABLE). And then do:

update TEMP_TABLE set COLUMNID=COLUMNID   (SELECT MAX(COLUMNID) 1 FROM LOG_COLUMN_INFO) order by COLUMNID desc;