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

"UPDATE" Is Not Updating Correctly Expand / Collapse
Author
Message
Posted Friday, August 10, 2012 8:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394
I am executing the SQL Statements on SQL Server 2008 (SSMS).

What am I missing? (I'm sure it's me, not the computer - ha, ha)

Here's the SQL Statement to Update:
UPDATE DIAPLUSDB.dbo.SalesForecast 
set JUL_SAL = isnull(JUL_SAL,0) + isnull(ss.SLS_DOL,0)
FROM
DIAPLUSDB.dbo.SalesForecast sf
left join SALES_SUMMARY ss on ss.CSCODE = sf.CustID and ss.PLT_NO = sf.PlantID
WHERE ss.PPPERIOD = '201207' and ss.SLS_DOL is not null

There may be multiple SALES_SUMMARY records for each CustID/PlantID.
When I check the results it appears that only the first SALES_SUMMARY record for each CustID/PlantID is added to JUL_SAL.

When I list the records, they all show up:
select sf.CustID, sf.PlantID, sf.JUL_SAL, ss.CSCODE, ss.PLT_NO, ss.SLS_DOL, ss.PPPERIOD
from DIAPLUSDB.dbo.SalesForecast sf
left join SALES_SUMMARY ss on ss.CSCODE = sf.CustID and ss.PLT_NO = sf.PlantID
WHERE ss.PPPERIOD = '201207' and ss.SLS_DOL is not null
order by sf.CustID, sf.PlantID

As you can see in the image, from the SELECT statement, CustID 1053 has 2 SALES_SUMMARY records, total SLS_DOL = 23,941.50. But, SalesForecast JUL_SAL = 11,751.00.


"The Road To Success Is Always Under Construction."

  Post Attachments 
SalesForecast Sample.jpg (11 views, 10.00 KB)
Post #1343451
Posted Friday, August 10, 2012 8:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
You should learn about aggregate functions.

In your case you will need to use SUM()



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1343459
Posted Friday, August 10, 2012 8:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394
Thanks. I started out trying to do just that - I use aggregate functions in SELECT statements.
I couldn't figure out how to construct the statement by including the SUM and could not find anything on the internet as an example to guide me.


"The Road To Success Is Always Under Construction."
Post #1343471
Posted Friday, August 10, 2012 9:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
You need to use subqueries.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1343489
Posted Friday, August 10, 2012 9:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
In your case you can use:


;WITH SUM_SALES_SUMMARY
AS
(
SELECT CSCODE AS CSCODE
,PLT_NO AS PLT_NO
,SUM(SLS_DOL) AS SUM_SLS_DOL
FROM SALES_SUMMARY
WHERE PPPERIOD = '201207'
)

UPDATE DIAPLUSDB.dbo.SalesForecast
SET JUL_SAL = ss.SUM_SLS_DOL
FROM DIAPLUSDB.dbo.SalesForecast AS sf
JOIN SUM_SALES_SUMMARY AS ss
ON ss.CSCODE = sf.CustID
AND ss.PLT_NO = sf.PlantID




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1343504
Posted Sunday, August 12, 2012 10:34 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
UPDATE sf
set JUL_SAL = isnull(sf.JUL_SAL,0) + isnull(ss.SLS_DOL,0)
FROM
DIAPLUSDB.dbo.SalesForecast sf
left join (
SELECT CSCODE, PLT_NO, SUM(SLS_DOL) AS Total_SLS_DOL
FROM SALES_SUMMARY
WHERE PPPERIOD = '201207' and SLS_DOL is not null
) ss on ss.CSCODE = sf.CustID and ss.PLT_NO = sf.PlantID

I'm not sure why do you need LEFT JOIN here.
If there are not corresponding records in SALES_SUMMARY the query will add 0 to JUL_SAL, leaving the value in there unchanged, but adding an overhead of a useless update.

Considering you WHERE clause effectively forced INNER JOIN anyway, I'd made it explicit.
Post #1343971
Posted Monday, August 13, 2012 1:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394
Thank you all for our assistance. I got a solution from Utter Access message board.

UPDATE DIAPLUSDB.dbo.SalesForecast
SET JUL_FOR = isnull(sf.JUL_FOR,0) +
( SELECT SUM(SLS_DOL)
FROM SALES_SUMMARY
WHERE (SALES_SUMMARY.CSCODE = sf.CustID)
AND (SALES_SUMMARY.PLT_NO = sf.PlantID)
AND (SALES_SUMMARY.PPPERIOD = '201207')
AND (SALES_SUMMARY.SLS_DOL is Not NULL) )
FROM DIAPLUSDB.dbo.SalesForecast sf, SALES_SUMMARY


The structure isn't intuitive to me, but, now I have an understanding of the structure and can construct the same in the future (I think).

Sergiy, you asked, "I'm not sure why do you need LEFT JOIN here."
I guess I don't have a comprehensive understanding of the "mechanics" of the JOIN - what I call 'under the hood'. In my mind the record processing, is to view each SalesForecast record, find matches in SALES_SUMMARY (CSCODE & PLT_NO), but do not include those outside the Period (PPPERIOD) and those with NULL SLS_DOL. One thing I know is that there are fewer SalesForecast records than SALES_SUMMARY.

Anyway, Thanks all again for your input.


"The Road To Success Is Always Under Construction."
Post #1344380
Posted Monday, August 13, 2012 10:24 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
EdA ROC (8/13/2012)
Thank you all for our assistance. I got a solution from Utter Access message board.

That's probably the worst possible one.

It delivers pretty much the same result as mine or Eugene's queries, but it creates a hidden loop: it runs a separate query against the source table for each row in the target table.
On tables with some reasonable number of records it will be terribly slow. It's especially dangerous if to consider remote call involvement.

Try the ones from above and compare the execution plans and overall performance.
Post #1344519
Posted Tuesday, August 14, 2012 1:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 7:44 AM
Points: 1,421, Visits: 3,220
I hate it when UPDATE doesn't update correctly.



The probability of survival is inversely proportional to the angle of arrival.
Post #1344991
Posted Wednesday, August 15, 2012 8:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394
Very funny Sturner - thanks for the smile.

I executed the 3 different statements as requested, each 3 times.

SALES_SUMMARY has 34,000 records, about 380 Bytes/record, data storage space is 14 MB
SalesForecast has 160 records, about 490 Bytes/record, .078 MB


The results are in microseconds:
Mine: 13, 13, 10
Sergiv: 6, 10, 6
Eugene: 6, 6, 10

The tables are small, but your point is taken - the difference is there.

Thanks for your help, I appreciate it.


"The Road To Success Is Always Under Construction."
Post #1345283
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse