Select statement and update statement have different results

[sschrupp]

As I was double checking some of my queries I discovered an oddity. An update query I have updates 12831 records, but the identical select query shows 13022 records.

Aroo??

The query updates one field to "X" based on some criteria. There's nothing special about the field. It's set as varchar(1) and to allow nulls.

Queries are below:

UPDATE Reporting_CurrentDetails
SET Runoff = 'X'
FROM PSME.CapCostFore.Reporting_CurrentDetails 
INNER JOIN PSME.CapCostFore.History_PriorDetails 
ON Reporting_CurrentDetails.AcctSchd = History_PriorDetails.AcctSchd 
AND Reporting_CurrentDetails.Idms_Region = History_PriorDetails.Idms_Region
WHERE (Reporting_CurrentDetails.Status_Code = '9') 
AND (MONTH(Reporting_CurrentDetails.Status_Date) 
= MONTH(DATEADD(month, - 1, CONVERT(date, SYSDATETIME()))))
AND (YEAR(Reporting_CurrentDetails.Status_Date) 
= YEAR(DATEADD(month, - 1, CONVERT(date, SYSDATETIME()))))
AND (History_PriorDetails.Status_Code <> '9')
AND ((Reporting_CurrentDetails.Status_Code 
+ Reporting_CurrentDetails.Term_Code) <> '9P') 

SELECT *
FROM PSME.CapCostFore.Reporting_CurrentDetails 
INNER JOIN PSME.CapCostFore.History_PriorDetails 
ON Reporting_CurrentDetails.AcctSchd = History_PriorDetails.AcctSchd 
AND Reporting_CurrentDetails.Idms_Region = History_PriorDetails.Idms_Region
WHERE (Reporting_CurrentDetails.Status_Code = '9') 
AND (MONTH(Reporting_CurrentDetails.Status_Date) 
= MONTH(DATEADD(month, - 1, CONVERT(date, SYSDATETIME()))))
AND (YEAR(Reporting_CurrentDetails.Status_Date) 
= YEAR(DATEADD(month, - 1, CONVERT(date, SYSDATETIME()))))
AND (History_PriorDetails.Status_Code <> '9')
AND ((Reporting_CurrentDetails.Status_Code 
+ Reporting_CurrentDetails.Term_Code) <> '9P')

I tried adding the full server.database.name and that didn't help. Changing the Month = stuff to just simply be month = 7, etc. didn't help.

What is going on here? Please help!

 

[markros]

In SQL Server 2005 and up:

;with cte as (myCurrentSelect statement)
select * from cte -- to verify

Now,

begin transaction

;with cte as (myCurrentSelect statement)
update cte set RunOff = 'X'

print @@@rowcount
rollback - first rollback to verify

If everything is OK, instead of rollback use commit transaction.

PluralSight Learning Library

 

[sschrupp]

Hi Markros, thanks for the reply.

I've never used CTE's before (still learning here), but I gave this a shot.

Same results as with just a straight query unfortunately.

I'm just not sure how this is possible. I tried inserting the data into a new table and trying it against the new table with no change. I was hoping the table was somehow corrupt or something.

I can list the records with a SELECT. They all exist. But the UPDATE only affects a certain number of them. Is there something that would keep a specific record from being updated?

I'm the only person in this table and I'm the admin and all that.

Oh, and I should mention that I run all sorts of other update queries on this same table involving different fields and they all work as expected. Select and Update queries have the same counts.

For some reason only this one fails.

 

[traingamer]

It's likely that multiple rows are found for a single key, thus only the last one written will make it to the table.

Check for duplicate keys in your select query.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

I should have said It's [blue]possible[/blue] as opposed to It's likely.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[sschrupp]

Yep! I was just coming back to post that it's because of duplicate results.

But this still confuses me. Neither table has duplicate records based on the AcctSchd and Idms_Region key provided in the query. So how am I getting a few duplicate lines in the select query like that?

This worries me because that means it's possible some of my other select queries we base our reports on could give us bogus results as well.

Any insight on how an inner join can give duplicate rows for SOME of the matches, but not all? Especially when there are no duplicate records in the source tables...

 

[markros]

If the relationship is 1 to many, then INNER JOIN will give duplicates (several records per 1 primary key in the parent table).

You'll get the similar result in Many to Many relationship.


PluralSight Learning Library

 

[sschrupp]

Well now I feel like an idiot.

There was supposed to be no duplicates in either table, making this a 1 to 1 query. Digging further into this I discovered that the older table DID have duplicates. Evidently we need to put an extra step into the process to get rid of duplicates.

I could have sworn we had that issue licked.

Thanks for helping out, everyone!