MySQL INSERT Error Operand should contain 1 column

Question

I have this MySQL insert query that is giving me a Error Code: 1241. Operand should contain 1 column(s).

INSERT INTO esjp_content
    ( esjp_content.primary_key, esjp_content.template_id, esjp_content.section_ref,
    esjp_content.position, esjp_content.indent, esjp_content.content,
    esjp_content.summary_id, esjp_content.role_ref, esjp_content.author_id,
    esjp_content.event_id, esjp_content.sys_time )
VALUES
    ( ( 3, 1, 1, 0, 0, 'Some test content.', 0, 1, 1, 69, UNIX_TIMESTAMP(NOW()) ),
    ( 4, 1, 1, 1, 1, 'Some test content2.', 0, 1, 1, 69, UNIX_TIMESTAMP(NOW()) ) )
ON DUPLICATE KEY UPDATE
    esjp_content.primary_key=VALUES(esjp_content.primary_key),
    esjp_content.template_id=VALUES(esjp_content.template_id),
    esjp_content.section_ref=VALUES(esjp_content.section_ref),
    esjp_content.position=VALUES(esjp_content.position),
    esjp_content.indent=VALUES(esjp_content.indent),
    esjp_content.content=VALUES(esjp_content.content),
    esjp_content.summary_id=VALUES(esjp_content.summary_id),
    esjp_content.role_ref=VALUES(esjp_content.role_ref),
    esjp_content.author_id=VALUES(esjp_content.author_id),
    esjp_content.event_id=VALUES(esjp_content.event_id),
    esjp_content.sys_time=VALUES(esjp_content.sys_time);

It works fine, if I only try to insert 1 record at at time, but I thought that INSERT allowed for inserting multiple records in a single statement. Any ideas?

P.S. I know the query is wordy, but that's fine, since it's generated programmatically.

`ON DUPLICATE KEY UPDATE` useless - you update nothing there. don't you? — Alex, Jun 29 '16 at 15:39
@Alex The only unique key in the table is `primary_key`. If the value already exists, I want every field in the record to be updated with whatever data is being passed. Otherwise, (the specified `primary_key` field should be zero, and) I want a new record to be added to the table. — Jim Fell, Jun 29 '16 at 15:42
@Alex I'm aware of that. It does nothing because it's getting an error. That's why I'm asking for help. If you have any constructive suggestions on how to make it better, I'm open to ideas. — Jim Fell, Jun 29 '16 at 15:46
I got the above error why using my ORM to insert data - I was trying to insert a Python list into a string column. — tschumann, Mar 07 '22 at 07:18

score 7 · Accepted Answer · answered Jun 29 '16 at 15:52

My bad, your query is ok. You just have some typos.

One extra open brace here: ( ( 3,

And one closing brace here: 69, UNIX_TIMESTAMP(NOW()) ) )

Guess if you fix that, everything should work fine.

http://sqlfiddle.com/#!9/1e9f3f/1

INSERT INTO esjp_content
    ( esjp_content.primary_key, esjp_content.template_id, esjp_content.section_ref,
    esjp_content.position, esjp_content.indent, esjp_content.content,
    esjp_content.summary_id, esjp_content.role_ref, esjp_content.author_id,
    esjp_content.event_id, esjp_content.sys_time )
VALUES
    (  3, 1, 1, 0, 0, 'Some test content.', 0, 1, 1, 69, NOW() ),
    ( 4, 1, 1, 1, 1, 'Some test content2.', 0, 1, 1, 69, NOW() + INTERVAL 1 DAY ) 
ON DUPLICATE KEY UPDATE
    esjp_content.primary_key=VALUES(esjp_content.primary_key),
    esjp_content.template_id=VALUES(esjp_content.template_id),
    esjp_content.section_ref=VALUES(esjp_content.section_ref),
    esjp_content.position=VALUES(esjp_content.position),
    esjp_content.indent=VALUES(esjp_content.indent),
    esjp_content.content='updated',
    esjp_content.summary_id=VALUES(esjp_content.summary_id),
    esjp_content.role_ref=VALUES(esjp_content.role_ref),
    esjp_content.author_id=VALUES(esjp_content.author_id),
    esjp_content.event_id=VALUES(esjp_content.event_id),
    esjp_content.sys_time=VALUES(esjp_content.sys_time);

Thanks! I've bookmarked SQL Fiddle. Haven't seen that site before, but looks like a great tool! :) — Jim Fell, Jun 29 '16 at 18:54
you are very welcome :-) that is why SO exists: to hep us share our knowledge and experience :-) good luck with your project — Alex, Jun 29 '16 at 19:27

MySQL INSERT Error Operand should contain 1 column

1 Answers1