public knowledge blog_load(sender Virendra)

                                                                                                                          Nothing shocks me, I am a Software Engineer.

Weird thing with inner queries in SQL SERVER 2005

Posted by Virendra Dugar on February 12, 2009

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. Just 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 behavior.

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

Initally I thought that this is a bug but actaully it’s not.  The inner query first tries to find the column in the current table(inner query’s table) and if it does not found then it will look for the outer query table. It is the best practice to use the tableName.ColumnName in the inner query. 

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

Enjoy…..

Waiting for your comments…..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: