INSERT INTO table VALUES.. vs INSERT INTO table SET Error

Question

I'm trying to run the following query:

"insert into visits set source = 'http://google.com' and country = 'en' and ref = '1234567890';"

The query looks good to me and it prints a warning:

1 row(s) affected, 2 warning(s): 1364 Field 'country' doesn't have a default value 1292 Truncated incorrect DOUBLE value: 'http://google.com'

And the info isn't stored as expected:

id , ref , bnid, source, country
'5', NULL, NULL, '0'   , ''

If I run the normal syntax like:

insert into visits (source,country,ref) values('http://google.com','en','1234567890');

I get the expected result:

'6', '1234567890', NULL, 'http://google.com', 'en'

The first syntax (insert set) was working in previous server. Now I changed to one with cpanel and it doesn't. It's the second time this week I get this problem in two different VPS with cpanel so I'm guessing it should be the version number or mysql config.

Mysql Version: 5.1.63-cll

Table:

CREATE TABLE `visits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ref` varchar(64) DEFAULT NULL,
  `bnid` int(11) DEFAULT NULL,
  `source` varchar(256) DEFAULT NULL,
  `country` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Any help?

score 11 · Accepted Answer · answered Jul 05 '12 at 17:05

11

Remove 'and' from the insert query

insert into visits set source = 'http://google.com' , country = 'en' , ref = '1234567890'

answered Jul 05 '12 at 17:05

Sabeen Malik

10,816
4
33
50

upps I'm quite ashamed of myself... Sorry – jribeiro Jul 05 '12 at 17:10
don't worry, happens to the best of us :) – Sabeen Malik Jul 05 '12 at 17:11

score 3 · Answer 2 · answered Jul 05 '12 at 17:06

3

When using INSERT...SET, separate the fields using ,, not AND.

INSERT INTO visits SET source = 'http://google.com', country = 'en', ref = '1234567890';

answered Jul 05 '12 at 17:06

gen_Eric

223,194
41
299
337

INSERT INTO table VALUES.. vs INSERT INTO table SET Error

2 Answers2