CODEWITHSUNDEEP
mysqlselectinsert
iopener
iopener

Reputation: 541

MySQL INSERT INTO ... SELECT throws Error 1064

Trying to duplicate some rows in a table but just change the ssreportid column from 4 to 6:

INSERT INTO ssreportparticipant (ssreportid, sssurveyparticipantid)
VALUES
SELECT 6, sssurveyparticipantid FROM ssreportparticipant 
WHERE ssreportid = 4

The error says #1064 near 'select 6, ...' but if I just run the select clause, it selects the records perfectly, with the new id of 6 in the ssreportid column.

The table has a primary key called ssreportparticipantid, and there is a unique key on (ssreportid, sssurveyparticipantid). Note that the select clause creates new records that have unique key pairs, so that's not the problem. I have tried putting brackets around the select clause, and even using two aliases for the table, no joy.

Using server version 5.0.45.

Please tell me that programmer fatigue has me missing a comma or something.

Thanks,

-Josh

Upvotes: 4

Views: 5582

Answers (2)

Erick
Erick

Reputation: 6089

I am not sure, but maybe it is possible that you cannot insert into a table with a select from the same table. Have you tried to select from a different table, just for the sake of testing ?

Upvotes: 0

ybo
ybo

Reputation: 17152

I think you should remove "VALUES"

Upvotes: 9

Related Questions