sql error: Unknown column in 'field list' on insert

Question

I am trying to insert an entry into a table, using Java, and it returns me an error "Unknown column XX in 'field list'".

For example: I have created a table using this line:

CREATE  TABLE `dbcs`.`born in` (`person` VARCHAR(100) ,`year` INT ,`prob` FLOAT);

the table was created successfully.

when I try to insert something to the table, it shows me the error. for example, the command:

INSERT INTO `dbcs`.`born in` VALUES (`Alanis Morissette`,1974,1.0)

will generate the error:

Unknown column 'Alanis Morissette' in 'field list'

Solution 1

Strings must be wrapped in quotes. You're using ticks which are not correct.

INSERT INTO `dbcs`.`born in` VALUES ('Alanis Morissette',1974,1.0)

Solution 2

use

INSERT INTO dbcs.born in VALUES ('Alanis Morissette',1974,1.0)

Comments

[myqyl4]

Delimit String values with quotes i.e. INSERT INTO dbcs.born in VALUES ('Alanis Morissette',1974,1.0)

[Kermit]

I think you need some backticks around born in. Also, I would emphasize that backticks should be used for system names.

[Pasha]

Thank you!! it helped a lot!! :)

[Endophage]

@Kermit I would have also added don't use spaces in names and avoid using a reserved keyword as a table/column name unless you really want to annoy somebody :-)

[Kermit]

@Endophage In the table name, yes.