7

I've read my threads about this problem but I still don't know how to solve it.

Error SQL query:

-- 
-- Dump data for table `bi_instituicoes` 
--
INSERT INTO `bi_instituicoes` (`id`, `Instituicao`, `Morada`, `Código Postal`, `Localidade`, `País`) 
VALUES (1, 'Escola Secundária D. Afonso Sanches', 'Alameda Flâmula Pais', NULL, 'Vila do Conde ', 'Portugal'), 
(2, 'Escola Secundária da Boa Nova', 'Av. dos Combatentes da Grande Guerra', NULL, 'Leça da Palmeira ', 'Portugal'), 
(3, 'Escola Secundária da Maia', 'Avenida Luís Camões', '4470-322', 'Maia', 'Portugal'), 
(4, 'Escola Secundária de Almeida Garrett', 'Praceta Doutor José Sampaio', NULL, 'Vila Nova de Gaia ', 'Portugal'), 
(5, 'Escola Secundária de José Gomes Ferreira', 'Rua José Sebastião e Silva', NULL, 'Lisboa', 'Portugal'), 
(6, 'Escola Secundária de Monserrate', 'R. Monserrate', NULL, 'Viana do Castelo ', 'Portugal'), 
(7, 'Escola Secundária de Paredes', 'R. Engenheiro Adelino A Costa , Castelões Cepeda', NULL, 'Paredes', 'Portugal'), 
(8, 'Escola Secundária de Raúl Proença, Leiria ', 'Rua João II[...]

MySQL said: Documentation

#1054 - Unknown column 'id' in 'field list'
ztirom
  • 4,382
  • 3
  • 28
  • 39
user3002293
  • 229
  • 3
  • 4
  • 13

6 Answers6

7

After hours of frustration with this issue, and trying to insert using every possible acceptable syntax, I found that the problem was a trigger on the table that I was inserting on. I haven't been able to find out why the Trigger caused the problem, but removing it allowed my inserts to work again...

Taylor Cox
  • 111
  • 1
  • 5
2

This can also be due to messed up triggers. Sometimes a show triggers can help.

ErichBSchulz
  • 15,047
  • 5
  • 57
  • 61
1

Try to replace:

INSERT INTO `bi_instituicoes` (`id`, ...

with:

INSERT INTO `bi_instituicoes` (id,

My guess is that the column id is not lower-case. This kind of confusion happens to many people.

Nir Alfasi
  • 53,191
  • 11
  • 86
  • 129
1

If you, like me, came here from sequelize in nodejs you need to know that

Sequelize will assume your table has a id primary key property by default. source

So you need to add a primaryKey: true option in the attribute of your model corresponding to your table's primary key.

Alessandro Sassi
  • 103
  • 1
  • 1
  • 10
0

Perhaps the table bi_instituicoes has no such field id - check its structure. Or if your Mysql by 5 version change query to:

-- 
-- Dump data for table `bi_instituicoes` 
--
INSERT INTO `bi_instituicoes`
VALUES (1, 'Escola Secundária D. Afonso Sanches', 'Alameda Flâmula Pais', NULL, 'Vila do Conde ', 'Portugal'), 
(2, 'Escola Secundária da Boa Nova', 'Av. dos Combatentes da Grande Guerra', NULL, 'Leça da Palmeira ', 'Portugal'), 
(3, 'Escola Secundária da Maia', 'Avenida Luís Camões', '4470-322', 'Maia', 'Portugal'), 
(4, 'Escola Secundária de Almeida Garrett', 'Praceta Doutor José Sampaio', NULL, 'Vila Nova de Gaia ', 'Portugal'), 
(5, 'Escola Secundária de José Gomes Ferreira', 'Rua José Sebastião e Silva', NULL, 'Lisboa', 'Portugal'), 
(6, 'Escola Secundária de Monserrate', 'R. Monserrate', NULL, 'Viana do Castelo ', 'Portugal'), 
(7, 'Escola Secundária de Paredes', 'R. Engenheiro Adelino A Costa , Castelões Cepeda', NULL, 'Paredes', 'Portugal'), 
(8, 'Escola Secundária de Raúl Proença, Leiria ', 'Rua João II[...]
  • It has. The fact is that I've exported that db from phpMyAdmin version 4.0.4.1 and imported in phpMyAdmin 2.11.8.1. And maybe is because of that old version ... – user3002293 Jan 14 '14 at 12:01
  • Try in parameters by export in Data creation options set syntax INSERT INTO tbl_name VALUES (1,2,3) –  Jan 14 '14 at 12:11
  • Please, don't ever recommend inserting data without explicit field list! This is bad habit and can cause a lot of problems later. – Arvo Aug 25 '15 at 11:54
-1

Simple solution is that a string must be within double quotations ".

Example:

INSERT INTO `bi_instituicoes`
VALUES (1, "Escola Secundária D. Afonso Sanches", "Alameda Flâmula Pais", NULL, "Vila do Conde ", "Portugal");
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Anoukh
  • 113
  • 4
  • Please, don't ever recommend inserting data without explicit field list! This is bad habit and can cause a lot of problems later. – Arvo Aug 25 '15 at 11:54
  • Can you explain that further? Why should that be needed? According to https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql, both single and double quotes should be fine – Nico Haase Jan 18 '20 at 23:24