Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Same tables different counts Expand / Collapse
Author
Message
Posted Friday, July 31, 2009 11:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:48 AM
Points: 56, Visits: 151
Good day all,
have a weird oddity here that has left me scratching my head.
I have a select qry that produces 1250 records but when I run it as a Update record it only updates 1185 records and I cannot figure out why they record count is different. I've stripped it down and simplified the Update qry but still the same wrong count.

The Select qry that produces the correct record count is as follows:

select *
FROM DBO.MyTable01
inner JOIN DBO.MyTable02 on
MyTable02.AcctNo = MyTable01.AcctNo
AND MyTable02.[Mdate] = MyTable01.[Mdate]
WHERE MyTable01.Type IN (10,810)
GO

And below the Update qry which produces a lower, incorrect record count.

UPDATE DBO.MyTable01
SET TPNTRT = '9999'
FROM DBO.MyTable01
inner JOIN DBO.MyTable02 on
MyTable02.AcctNo = MyTable01.AcctNo
AND MyTable02.[Mdate] = MyTable01.[Mdate]
WHERE MyTable01.Type IN (10,810)
GO

Can anyone see why such a thing is happening, I cannot make sense of it.

Thanks,
Mitch.....
Post #763288
Posted Friday, July 31, 2009 11:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
It's probably that you're getting "join multiplication". How many rows does the table you're updating actually have that fit the Where and Join criteria?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #763290
Posted Friday, July 31, 2009 9:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
I agree with Gus... if there are any duplicates in Mytable02 according to the WHERE clause, the SELECT will find them and return them. Updates, however, will only update a row in MyTable01 only once no matter how many "identical" rows exist in MyTable02. Some loath that fact... I frequently count on it. Oracle won't even let you do it.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #763511
Posted Monday, August 3, 2009 10:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:48 AM
Points: 56, Visits: 151
Thanks for the input guys.
You may be right about the multiple records but before I left here on Friday I ran a test update and entered a bogus but easily identifiable value for the field and despite saying 1507 records updated, the full 1587 records were updated with that value. Very strange unless SQL does not count the multiple records with an update.

Anyway, unless I can think of a way of discovering anything else that may play a factor in this the records I wanted to update have updated correctly.

Cheers again.
Post #764229
Posted Monday, August 3, 2009 11:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 23,089, Visits: 31,635
How many rows are returned with the following query?

select
mt1.*
from
DBO.MyTable01 mt1
where
mt1.Type IN (10,810)
and exists (select 1 from dbo.MyTable2 mt2 where mt1.AcctNo = mt2.AcctNo and mt1.Mdate = mt2.Mdate)


Edit: Modified code slightly.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #764253
Posted Wednesday, August 5, 2009 9:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:48 AM
Points: 56, Visits: 151
Hi Lynn,
thanks for the reply and forgive my delay but things here have been swamped.
I ran your Select qry and strangely enough only got the 1507 records and not the 1587 I expected.

Any thoughts on this?
Post #765615
Posted Wednesday, August 5, 2009 11:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 23,089, Visits: 31,635
Mitch2007 (8/5/2009)
Hi Lynn,
thanks for the reply and forgive my delay but things here have been swamped.
I ran your Select qry and strangely enough only got the 1507 records and not the 1587 I expected.

Any thoughts on this?


Is it a one to many relationship between the two tables?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #765698
Posted Thursday, August 6, 2009 12:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:48 AM
Points: 56, Visits: 151
It's a many to many relationship. Although there are duplicate Account numbers in table one and duplicate account numbers in table 2 there can only be one account number with a specific date in either of the two tables. The tables are joined using both Acct and Month using an inner join.

Lynn thanks so much for your time on this, it is very much appreciated.
Post #766469
Posted Thursday, August 6, 2009 12:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
Mitch2007 (8/6/2009)
It's a many to many relationship. Although there are duplicate Account numbers in table one and duplicate account numbers in table 2 there can only be one account number with a specific date in either of the two tables. The tables are joined using both Acct and Month using an inner join.

Lynn thanks so much for your time on this, it is very much appreciated.


If there is morth than a one year span in the table, joining by month (unless it's a month offset provided by DATEDIFF) would certainly be the source of some unexpected row counts.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #766481
Posted Thursday, August 6, 2009 9:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 23,089, Visits: 31,635
What does the following query return?

select
mt1.AcctNo,
mt1.Mdate,
count(*) as RecCnt
from
dbo.MyTable01 mt1
inner join dbo.MyTable2 mt2
on (m1.AcctNo = mt2.AcctNo
and m1.Mdate = mt2.Mdate)
group by
mt1.AcctNo,
mt1.Mdate
having
count(*) > 1
order by
mt1.AcctNo,
mt1.Mdate;




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #766689
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse