CODEWITHSUNDEEP
mysqlindexing
Stattrav
Stattrav

Reputation: 151

Unexpected behaviour of the query

This particular query is supposed to enter the user into the database.

This query does not always insert the values of the firstname and the lastname fields along with others. firstname and lastname are empty for a few insertions and for the others its working as expected.

INSERT INTO `users` (mobile, passwordHash, firstname, lastname, ent_id, email )
VALUES ('913800341127', '678a1491514b7f1006d605e9161946b1', 'nat', 'sam', '108', NULL)
ON DUPLICATE KEY UPDATE `firstname` = VALUES(firstname),`lastname` = VALUES(lastname)

Related Info:

CREATE TABLE `users` (
 `id` int(11) NOT NULL auto_increment,
 `tag` varchar(5) NOT NULL default 'ind',
 `username` varchar(50) default NULL,
 `firstname` varchar(100) default NULL,
 `lastname` varchar(100) default NULL,
 `passwordhash` varchar(255) NOT NULL,
 `secretq` varchar(255) default NULL,
 `secreta` varchar(100) default NULL,
 `email` varchar(50) default NULL,
 `mobile` varchar(13) default NULL,
 `last_login` datetime default NULL,
 `ent_id` bigint(20) NOT NULL default '1',
 `is_inactive` tinyint(1) NOT NULL COMMENT 'Whether the user is active or not',
 PRIMARY KEY  (`id`),
 UNIQUE KEY `mobile_2` (`mobile`,`ent_id`),
 UNIQUE KEY `email_2` (`email`,`ent_id`),
 KEY `username` (`username`),
 KEY `ent_id` (`ent_id`,`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Upvotes: 2

Views: 101

Answers (1)

xkeshav
xkeshav

Reputation: 54016

syntax error it should be :

ON DUPLICATE KEY UPDATE firstname = 'nat',lastname = 'sam'

Upvotes: 1

Related Questions