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.

Let's go through the script.

• Create a new fresh database.
  
  CREATE DATABASE DB_TEST

• Use this Database.
  
  USE DB_TEST

• Create a table name 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),
      IsDamaged 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 statements to confirm that data is entered or not.
  
  SELECT * FROM tblCategory
  SELECT * FROM tblProduct

• Here is a select query which is incorrect. The query tells that select ProductID from tblCategory where categoryId = 1, But the tblCategory table is not having a column named ProductID. So when we execute this query, it throws an error and that is the expected behaviour.
  
  SELECT ProductID FROM tblCategory WHERE CategoryID = 1

• Here is the magic. I have used the above incorrect select query with a update statement as an inner query. What do you think, what should happen when you execute this query? This query should throw an error as my inner select query is not correct. But just execute this query and you will be shocked.
  
  UPDATE tblProduct SET IsDamaged = 1
  WHERE ProductID IN
  (SELECT ProductID FROM tblCategory WHERE CategoryID = 1)

• Oops!!!! 10 rows affected. All the data in IsDamaged is set to 1 but my inner select query(SELECT ProductID FROM tblCategory WHERE CategoryID = 1) is wrong.

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 IsDamaged = 1
WHERE ProductID IN
(SELECT tblCategory.ProductID FROM tblCategory WHERE CategoryID = 1)

Now this inner query will throw an error. So next time be careful whenever you are working with inner queries.

License