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

Question

I am 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 displays 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 information is not saved as expected:

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

If I run the usual syntax, for example:

 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) worked on the previous server. Now I switched to one with cpanel, and it is not. This is the second time this week I get this problem in two different VPS with cpanel, so I assume 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?

Answer 1

Remove "and" from insert request

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

Answer 2

When using INSERT...SET separate the fields using, rather than AND .

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