Insert query syntax error when run using phpmyadmin

Question

I am trying to insert data using the following query

INSERT INTO events(
    '_id', 
    'id', 
    '_kafka.topic', 
    '_kafka.partition', 
    '_kafka.offset', 
    '_kafka.timestamp', 
    '_kafka.key', 
    '_kafka.simulated', 
    '_kafka.consumed'
)
VALUES(
    "{'$oid': '5daa639e4176a8d0301beb0c'}", 
    '0', 
    'Events_227_v3', 
    '2', 
    '22938980', 
    "{'$date': '2019-10-19T01:15:10.355Z'}", 
    "b'0'", 
    'False', 
    "{'$date': '2019-10-19T01:15:10.913Z'}"
)

Error message

`#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''_id', 'id', '_kafka.topic', '_kafka.partition', '_kafka.offset', '_kafka.timest' at line 1

What is causing this error?

there won't be any quotes for column names ''. Instead use back ticks ` — James
– James, Commented Nov 6, 2019 at 9:47
INSERT INTO events(_id, id, _kafka.topic, _kafka.partition, _kafka.offset, _kafka.timestamp, _kafka.key, _kafka.simulated, _kafka.consumed) VALUES("{'$oid': '5daa639e4176a8d0301beb0c'}", '0', 'Events_227_v3', '2', '22938980', "{'$date': '2019-10-19T01:15:10.355Z'}", "b'0'", 'False', "{'$date': '2019-10-19T01:15:10.913Z'}") when I used this I get 1054 (42S22): Unknown column '_kafka.topic' in 'field list' — Aman Khandelwal
– Aman Khandelwal, Commented Nov 6, 2019 at 9:56
If you got your answer make as "Accepted" use the tick button on left side of answer — James
– James, Commented Nov 6, 2019 at 11:42

James · Accepted Answer · 2019-11-06 10:02:09Z

1

On Insert the column name shouldn't enclosed using single ' or double quotes ".

Instead mention just the column name or use back ticks (`).

Suppose you have table like events and _kafka

And when executing the below Insert query

INSERT INTO events(id, _kafka.topic) VALUES( '1', 'Events_227_v3')

It will throw an error Unknown column '_kafka.topic' in 'field list'

since the column topic doesn't exists in the table events.

answered Nov 6, 2019 at 10:02

James

1,8292 gold badges11 silver badges21 bronze badges

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

2 Comments

Aman Khandelwal Over a year ago

column existed...I figured a way through. Thanks for helping

James Over a year ago

Yes you should use back ticks which i have mentioned in comment as well as in answer

Aman Khandelwal · Accepted Answer · 2019-11-06 10:03:53Z

0

The column names have a dot used in them.

Thus they required to be in following manner

INSERT INTO events(`_id`, `id`, `_kafka.topic`, `_kafka.partition`, `_kafka.offset`, `_kafka.timestamp`, `_kafka.key`, `_kafka.simulated`, `_kafka.consumed`) VALUES("{'$oid': '5daa639e4176a8d0301beb0c'}", '0', 'Events_227_v3', '2', '22938980', "{'$date': '2019-10-19T01:15:10.355Z'}", "b'0'", 'False', "{'$date': '2019-10-19T01:15:10.913Z'}")

Usage of ` symbol was required alongwith the column names.

For example, if column name is col.name then in the query it should be like `col.name`

answered Nov 6, 2019 at 10:03

Aman Khandelwal

1482 silver badges13 bronze badges

Collectives™ on Stack Overflow

Insert query syntax error when run using phpmyadmin

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related