Sunday, November 21, 2010

Something about WHERE conditions

Something about WHERE conditions:

Once I was writing a sql statement like this :

 UPDATE Sec45DailyTables.DBO.DropRouteProcessingTable
 SET DropBusOutTime  = B.DropBusOutTime ,
 DropBusInTime  = B.DropBusInTime 
 FROM sec45DailyTables.dbo.DailyAttendanceTable20101119  B

In essence, there were two tables  Sec45DailyTables.DBO.DropRouteProcessingTable and
sec45DailyTables.dbo.DailyAttendanceTable20101119 , each having same set of records.

What I wanted to do was to update target table Sec45DailyTables.DBO.DropRouteProcessingTable,
to same DropBusOutTime values from source table, sec45DailyTables.dbo.DailyAttendanceTable20101119.
To avoid a full table update, I decided to put a filter, like this :
Update only those rows where source table has non null values and destination table has
null values.

 UPDATE Sec45DailyTables.DBO.DropRouteProcessingTable
 SET DropBusOutTime  = B.DropBusOutTime ,
 DropBusInTime  = B.DropBusInTime 
 FROM sec45DailyTables.dbo.DailyAttendanceTable20101119  B
 WHERE
 B.DropBusOutTime IS NOT NULL
 AND
 Sec45DailyTables.DBO.DropRouteProcessingTable.DropBusOutTime IS NULL

There were around 2500 total records in each table, and 1160 rows where in source table where
DropBusOuttime value was not null.
So I expected that because of the where clause condition ( B.DropBusOutTime IS NOT NULL ) only
1160 rows will be updated.
But to my surprise,
all 2500 rows were updated.
Then I changed the statement to the check the ID (like in a join condition) :
 UPDATE Sec45DailyTables.DBO.DropRouteProcessingTable
 SET DropBusOutTime  = B.DropBusOutTime ,
 DropBusInTime  = B.DropBusInTime 
 FROM sec45DailyTables.dbo.DailyAttendanceTable20101119  B
 WHERE
 B.DropBusOutTime IS NOT NULL
 AND
 Sec45DailyTables.DBO.DropRouteProcessingTable.DropBusOutTime IS NULL
 AND
 ltrim(rtrim(Sec45DailyTables.DBO.DropRouteProcessingTable.cardno)) =
 ltrim(rtrim(B.CardNo ))
And now it worked.
What I assume is that SQL worked on both the conditions earlier,
and it worked literally, without combining the conditions, because, there was no
condition which worked on both tables. Hence conditions were applied separately, and
since the destination table was having NULL in all rows, all its rows were updated (with null of course).
However, in the later condition, since there was a joining condition applied, it (the joining
condition)
worked on both tables. 

No comments:

Post a Comment