SQL 2005 Update - number of counts issue

Question

I am using SQL 2005. When i am selecting a set rows from the table with the appropriate where condition, it return value as 31. The same select value if i update to another table it is updating as 30. i used the SQL update as follows

update tablename
set column1 = (Select column2 from tablename where month(field1) = 05 and year(field1) =2010)
where field 2 = 'xxx'

If i execute the inner select alone, it returns a value in column2 as 31. Once the update executed and select from the updated table, it shows as 30.

Only 1 number difference for many rows...

Could anybody face this problem...

Answer 1

Without the table definition this will be very difficult, but the first thing to check if there are any triggers on the table that change the value. The second thing is start debugging, which means breaking your code up:

declare @column2 as int

Select @column2 = column2 from tablename where month(field1) = 05 and year(field1) =2010

--Check whether @column2 is equal to 31.
select @column2

update tablename
set column1 = @column2
where field 2 = 'xxx'

--Check whether column1 in tablename is equal to 31
select column1
from tablename
where field 2 = 'xxx'

I am guessing that the problem is that you mixed up some of the column names, you used 'column1', 'column2', 'field1' and 'field 2' all from one table.