SQL 2005 Update - number of counts issue

I am using MS SQL Server 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 f开发者_开发问答ace this problem...

I'm going through posts that are marked unanswered since joining this awesome site... not sure if that's the correct approach for older posts, but until someone tells me to stop I guess I'll keep going lol.

So here's what I see with this issue:

If you know you can SELECT the total record set, then you can immediately move to what you are trying to do with it beyond the SELECT.

I note that you are not trying to INSERT the result, but it's an UPDATE. Since you are updating existing rows, and since your only clause is your WHERE field 2 ='xxx', then you can be sure that you have one row not matching your WHERE. Sounds like a simple data integrity issue for that row.

To find the offending row, do a query against your table either as a NOT IN or <> depending on the type of data.

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.