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
– Alex, Commented Jun 29, 2016 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
– Jim Fell, Commented Jun 29, 2016 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
– Jim Fell, Commented Jun 29, 2016 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
– tschumann, Commented Mar 7, 2022 at 7:18

Alex · Accepted Answer · 2016-06-29 15:52:21Z

7

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);

answered Jun 29, 2016 at 15:52

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jim Fell Over a year ago

Thanks! I've bookmarked SQL Fiddle. Haven't seen that site before, but looks like a great tool! :)

Alex Over a year ago

you are very welcome :-) that is why SO exists: to hep us share our knowledge and experience :-) good luck with your project

Collectives™ on Stack Overflow

MySQL INSERT Error Operand should contain 1 column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related