SQL Update Statement can go crazy!!!

We had a production issue today and to rectify it i need to update some  records in production. As I dont have access to production database , I  raised a help desk ticket with the SQL statements.

I have to provide two statement which are as follows

[1] Update the “active”  column value to 1 and reference id column to NULL for for ONE  payer (example payer with primary Key : 100).

[2] Mark the “active” column to 0 and referenced id column to 100 (referencing payer with ID = 100) for another 10 payers.

DBA executed the query but came back telling me that the first query updated one record as expected, where as the second query did not update any columns.

I was so surprised as it was  simple SQL query and why it got failed.  I found the reason and corrected it. I think it was an interesting problem and so I decided to write about it with an example.

Step-1 : Creating a Person Table

I created a person table as follows

[code]

CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`married` tinyint(1) DEFAULT NULL,
`address1` varchar(20) DEFAULT NULL,
`city` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

[/code]

Step-2: Populating values into tables

I inserted some records into the table using simple insert script.

[code]
insert into `person`(`id`,`name`,`age`,`married`,`address1`,`city`) values (1,’James’,10,0,’City Sq Road’,’CA’);
insert into `person`(`id`,`name`,`age`,`married`,`address1`,`city`) values (2,’John’,1,1,’State Sq Road’,’CA’);
insert into `person`(`id`,`name`,`age`,`married`,`address1`,`city`) values (3,’Adam’,15,0,’State Sq Road’,’CA’);
insert into `person`(`id`,`name`,`age`,`married`,`address1`,`city`) values (4,’Bebo’,20,1,’State Sq Road’,’CA’);
insert into `person`(`id`,`name`,`age`,`married`,`address1`,`city`) values (5,’Bnecy’,20,1,’State Sq Road’,’CA’);
insert into `person`(`id`,`name`,`age`,`married`,`address1`,`city`) values (6,’Zulu’,13,0,’State Sq Road’,’CA’);

[/code]

Step-3: Query the table – person

I then queried the table – person to make sure all looks good.

Step-4: Execute the update statement

I executed the update statement and checked how it has impacted the table.

I executed a query which will change the age and married status of a person with Primary Key ID = 2

I expected ONE record to be updated and query result gives the expected result.

Step-5: Check the Result

I checked the modified data with a simple SELECT statement.

Surprise !! Surpise!!

I could see the age changed to 0 (ZERO) and married status remain 0 (ZERO) instead of 1 (ONE)

My query was to change the age to 20 and married status to ZERO.

Step-5: Troubleshooting

If you closely monitor the Query I executed you could notice the problem.

[code]

update person set age= 20 and married = 0 where id=2

[/code]

I used an “and” between “age= 20”  and “married = 0”.

The correct query should be as follows with COMMA instead of AND.

[code]
<h2>update person set age= 20 <strong>,</strong> married = 0 where id=2</h2>
[/code]

Why MYSQL did not Complained?

My next thought was if the query was wrong I should have end up in a Syntax error instead of executing it wrongly. So the syntax MUST be correct.

On further experimentation I noticed that MYSQL uses the right hand side of assignment as an expression and “AND” is a valid operand.

“age= 20 and married = 0″

So it converted the expression into a boolean and returns false. The value of false is ZERO in MYSQL and it stored that value against “age”.  The column “married” is not considered for update as it is already treated as an expression in the value for “age”.

Lessons Learned

Most of the time we check the query for syntax check before pass it for execution in production environment. We sometimes believe that if syntax is correct our query will work.  The above example proves it wrong!!

SO BEWARE WHEN YOU PREPARE UPDATE STATEMENTS IN SQL!!!

Posted in: Programming

Leave a Comment