Saturday, November 29, 2008

Weird thing with inner queries in SQL SERVER 2005

Few days ago, I found a very strange thing with SQL SERVER 2005 and I strongly feel that THIS IS A BUG in SQL SERVER which actually can create lots of data discrepancy.

To prove my point I have created a sample SQL Script. I want you to run the script in SQL SERVER.

Details of the Script

· Create new database.

CREATE DATABASE DB_TEST

· Use this Database.

USE DB_TEST

· Create a table named tblCategory

CREATE TABLE tblCategory
(
CategoryID INT PRIMARY KEY,
CategoryNAME VARCHAR(50)
)

· Create another table named tblProduct

CREATE TABLE tblProduct
(
ProductID INT PRIMARY KEY,
CategoryID INT FOREIGN KEY REFERENCES tblCategory(CategoryID),
ISCOMPLETED BIT
)

· Insert 5 rows in tblCategory

INSERT INTO tblCategory VALUES (1,'Category1')
INSERT INTO tblCategory VALUES (2,'Category2')
INSERT INTO tblCategory VALUES (3,'Category3')
INSERT INTO tblCategory VALUES (4,'Category4')
INSERT INTO tblCategory VALUES (5,'Category5')

· Insert 10 rows in tblProduct

INSERT INTO tblProduct VALUES (1,1,0)
INSERT INTO tblProduct VALUES (2,1,0)
INSERT INTO tblProduct VALUES (3,2,0)
INSERT INTO tblProduct VALUES (4,2,0)
INSERT INTO tblProduct VALUES (5,3,0)
INSERT INTO tblProduct VALUES (6,3,0)
INSERT INTO tblProduct VALUES (7,4,0)
INSERT INTO tblProduct VALUES (8,4,0)
INSERT INTO tblProduct VALUES (9,4,0)
INSERT INTO tblProduct VALUES (10,5,0)

· Select statement to confirm the data is inserted or not.

SELECT * FROM tblCategory
SELECT * FROM tblProduct

· Here is a select query which is not correct. The query tells select ProductID from tblCategory table where CategoryID = 1. But the problem here is tblCategory is not having a column named ProductID. So this query throws an error and that's a correct behaviour.

SELECT ProductID FROM tblCategory WHERE CategoryID = 1

· Here is the magic. I have used the above select query which is not correct as a inner query with a update statement. What to do you think? What will happen? Update query should throw an error. Logically it should.I was thinking the same But just execute this update query and you will be shocked.

UPDATE tblProduct SET IsCompleted = 1 WHERE ProductID IN (SELECT ProductID FROM tblCategory WHERE CategoryID = 1)

· Oops, 10 rows affected. Surpised. All the data in IsCompleted field is set to 1 but my inner query (SELECT ProductID FROM tblCategory WHERE CategoryID = 1) is wrong.

· This is just a sample query but When I executed such a similiar statement, 3364 rows in my table was updated.

SELECT * FROM tblCategory
SELECT * FROM tblProduct

I think this is a bug but actaully it's not. Please read comments for more information on this..


back to top