Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Same tables different counts


Same tables different counts

Author
Message
Mitch2007
Mitch2007
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
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.....
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45026 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mitch2007
Mitch2007
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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.

Cool
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)
Mitch2007
Mitch2007
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
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?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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?

Cool
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)
Mitch2007
Mitch2007
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45026 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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;



Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search