January 4, 2011 at 9:40 am
SQL Server 2008
Hi, I have the two below statements which as far as I'm aware should return the same row count when executed, but do not.
tables:
NewStdCost (1146 rows) and stockm (12000+ rows)
A) I firstly checked via SELECT that all 1146 rows in my temp table (NewStdCost) were matched correctly to my live table (stockm). They were, the statement returned all 1146 rows from my live table.
select s.warehouse, s.product,s.analysis_c, n.NewStdCost,
(n.NewStdCost - (s.standard_labour+s.standard_overhead+s.standard_subcontra)) as MATCOST
from stockm s, NewStdCost n
where s.warehouse = n.WarehouseCode and s.product = n.ProductCode and s.analysis_c = n.Currency
B) Using exactly the same conditions for my WHERE clause I then updated my live table but the sql reported that it had only updated 694 records. I repeated this a few times just to make sure and it reported same figure everytime regardles of how I coded it.
-- update s set standard_cost = n.NewStdCost , standard_material = (n.NewStdCost - (s.standard_labour+s.standard_overhead+s.standard_subcontra))
from stockm s, NewStdCost n
where s.warehouse = n.WarehouseCode and s.product = n.ProductCode and s.analysis_c = n.Currency
However, upon checking the data it seems all 1146 rows in my live table have the correct data from my temp table so all 1146 were updated. And even if they were originally the same, surely the update wouldn't have cared anyway and still updated/reported all 1146 reporting back 1146 rows, not just 694 as it did.
I am new to sql2008, previouslly using sql 2000 most of time and a little 2005 so is this a 'feature' I'm not aware of because it doesn't lend itself to debugging or analysis verification at all.
Thanks
Richard
January 4, 2011 at 9:45 am
Your join is multiplying the number of rows in the target table.
Try this:
CREATE TABLE #T1 (
ID INT IDENTITY PRIMARY KEY,
Col1 CHAR(1));
CREATE TABLE #T2 (
ID INT IDENTITY PRIMARY KEY,
T1ID INT,
ColA CHAR(1));
INSERT INTO #T1 (Col1)
VALUES ('a'),('b');
INSERT INTO #T2 (T1ID, ColA)
VALUES (1,'c'),(1,'d'),(2,'e');
SELECT *
FROM #T1 AS T1
INNER JOIN #T2
ON T1.ID = #T2.T1ID
WHERE T1.ID = 1;
UPDATE T1
SET Col1 = 'f'
FROM #T1 AS T1
INNER JOIN #T2
ON T1.ID = #T2.T1ID
WHERE T1.ID = 1;
You'll see that the Select returns 2 rows, the update modifies 1 row, and if you run the Select again, "both rows" are affected.
- 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
January 4, 2011 at 10:01 am
Annoyed with myself now! The customer told me they were unique records and as the select /update was based on unique key I didn't stop to think that there may obviously be duplication, I took it on faith, fatal error!
Must be the xmas holiday excess but thanks for putting me back on the path of sensibility.
That' the years' first "how to make yourself look a plonker in one fell swoop" over and done with.
Thanks
January 4, 2011 at 10:05 am
Just keep telling yourself "It's a common mistake. Any plonk ... anyone could have made it." You'll feel better. :hehe:
Honestly, it is a common mistake. It usually comes up in tables with 1:many relationships or many:many, when updating one side of it. Are these meant to be unique in both tables?
- 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
January 4, 2011 at 10:56 am
The records in his spreadsheet that I created my temp table from were supposed to be unique so I never created my own table key but still shoulda done a unique count on the data just to make sure. If I was new to sql I'd accept it as a common mistake but even for my basic level it is a bit schoolboyish! Happy New Year to you
Thanks
January 4, 2011 at 2:10 pm
Yes, Merge is better than Update From. I didn't notice this was 2k8 or I'd have mentioned that too.
- 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
January 4, 2011 at 4:33 pm
CELKO (1/4/2011)
It would also help if your data elements kept the same name from table to table and you followed ISO-11179 rules. For example, a table is a set so its name is always a collective or plural. A column is scalar so its name is singular. Therefore they cannot have the same name in a good data model
This table is likely part of a Sage Enterprise application (Line 500/Sage 1000) and the names are what they are and what they have been for near on 30 years.... you can ask for standards all you like but if the software companies don't use them, you can't do a thing about it!
But you are right - the system has a terrible data model !
Your attempts to guess the completion of those seemingly incomplete names shows this - analysis_c is analysis_c - it comes between analysis_a and analysis_d ! 😉
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 5, 2011 at 5:51 am
Oh yes on all accounts!! Its a terrible data model and one I particularly hate dealing with since nothing from back end through to user front end is consistent and I can never see how Sage has survived the ages. But thats a story for another forum 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply