SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"UPDATE" Is Not Updating Correctly


"UPDATE" Is Not Updating Correctly

Author
Message
EdA ROC
EdA ROC
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 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."
Attachments
SalesForecast Sample.jpg (14 views, 9.00 KB)
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12422 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
EdA ROC
EdA ROC
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 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."
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41991 Visits: 19829
You need to use subqueries.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12422 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25620 Visits: 12467

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.
EdA ROC
EdA ROC
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 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."
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25620 Visits: 12467
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.
sturner
sturner
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5106 Visits: 3259
I hate it when UPDATE doesn't update correctly.

The probability of survival is inversely proportional to the angle of arrival.
EdA ROC
EdA ROC
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 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."
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