Same tables different counts

Question

Same tables different counts

Mitch2007

SSC Eights!

Points: 830
More actions
July 31, 2009 at 11:29 am

#212962

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.....

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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 SSC Guru Points: 1004339 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mitch2007 SSC Eights! Points: 830 More actions · Answer 3

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 SSC Guru Points: 442467 More actions · Answer 4

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.

Mitch2007 SSC Eights! Points: 830 More actions · Answer 5

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 SSC Guru Points: 442467 More actions · Answer 6

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?

Mitch2007 SSC Eights! Points: 830 More actions · Answer 7

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 SSC Guru Points: 1004339 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

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;