update and select record count mismatch

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

update and select record count mismatch

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-09-26 : 16:07:55

Hi,

When I run a same join in select and update, I get different number of record count.

Here is the example, It updates only 1070 records.

Update a
set a.materialcost = b.cost
FROM tbl_A a
inner join tbl_B b on a.itemnumber = b.itemnumber
where b.cost <> 0

On Select, it returns 1085 records

Select
a.materialcost , b.cost
FROM tbl_A a
inner join tbl_B b on a.itemnumber = b.itemnumber
where b.cost <> 0

Is this possible or I am missing something here.

thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 16:10:49

It means you have duplicates itemnumber in the tbl_B table.

try this


Select distinct
a.materialcost , b.cost
FROM tbl_A a
inner join tbl_B b on a.itemnumber = b.itemnumber 
where b.cost <> 0

E 12°55'05.25"
N 56°04'39.16"

thanksfor help
Posting Yak Master

106 Posts

Posted - 2007-09-26 : 16:19:50

Thank you so much.

Distinct cleared the confusion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources