MySQL INSERT INTO ... SELECT throws Error 1064

Question

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

Answer 1

I think you should remove "VALUES"

Answer 2

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 ?

Answer 3

i use mambo 4.6..... i can create a table i can't insert nothing|why? this is the code: insert into ana(nome, cognome) values ('alex','gamba');