Solved

nested update query with inner join problem

Posted on 2011-02-24
10
360 Views
Last Modified: 2012-06-21
What's wrong with the query below. I have a table, which has duplicate records in it (not total duplicated but mostly). So, with those duplicate records, I want to add the values in the [CUBs Cr Amt €] field together, and put the summed value back into each value of the [CUBs Cr Amt €] field.

So, instead of having two records, with on record having a value of 10 in that field, and the other records having a value of 20 in that field, both records now show a value of 30, with other records being unaffected (i.e. if it doesn't have a duplicate and it's figure previuously was 15, it is now still 15).

Anyway tried doing that with the query below and it didn't work. Some kind of syntax error. What's the issue?
strSQL = "UPDATE [CTC Table]"
strSQL = strSQL + " SET [CTC Table].[CUBs Cr Amt €] = Temp.[CUBs Cr Amt €]"
strSQL = strSQL + " FROM [CTC Table]"
strSQL = strSQL + " INNER JOIN (SELECT SUM(CUBs Cr Amt €) FROM [CTC Table] GROUP BY [CARA Ref]) As Temp"
strSQL = strSQL + " ON [CTC Table].[CARA Ref] = Temp.[CARA Ref]"

Open in new window

0
Comment
Question by:AidenA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34969511
see this article that explains how to do UPDATE/JOIN:
http://www.experts-exchange.com/A_1517.html

you cannot do a GROUP BY in a UPDATE directly...
you will hence likely need another syntax (see article above)
0
 

Author Comment

by:AidenA
ID: 34969895
Hi thanks, had a look at that but there's quite a lot of information. well firstly this is MS Access (although I will probably be migrating to oracle at some stage), so it looks like you're saying the structure is wrong to start with it should be more like

UPDATE tbl_Employees e
INNER JOIN stage_Employees s ON s.ssn = e.ssn
  SET e.last_name = s.last_name
    , e.first_name = s.first_name
WHERE s.ssn LIKE "123*"

So the inner join is in the wrong place and the FROM shouldn't be included either?

So, you say that group by won't work... can you point me to what I should be looking for to make this query work? or where in your article i should look for this?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34970079
your update will need to do a subselect:
UPDATE [CTC Table] t
   SET [CUBs Cr Amt €] = ( SELECT SUM(x.[CUBs Cr Amt €] 
               FROM [CTC Table] x
              WHERE x.[CARA Ref] = t.[CARA Ref]
              )

Open in new window

0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:AidenA
ID: 34970348
ok well works in two steps but means i have to create a table and delete it... which obviously isn't the best

strSQL = "SELECT [CARA Ref], SUM([CUBs Cr Amt €]) AS [CUBs Cr Amt €]"
strSQL = strSQL + " INTO Duplicates"
strSQL = strSQL + " FROM [CTC Table]"
strSQL = strSQL + " GROUP BY [CARA Ref]"

strSQL = "UPDATE [CTC Table] C"
strSQL = strSQL + " INNER JOIN Duplicates D"
strSQL = strSQL + " ON C.[CARA Ref] = D.[CARA Ref]"
strSQL = strSQL + " SET C.[CUBs Cr Amt €] = D.[CUBs Cr Amt €]"
0
 

Author Comment

by:AidenA
ID: 34970375
sorry, missed your last post... let me just try it...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34970409
yes, the 2 steps (actually 3 steps to drop the table afterwards) shall work, of course :)
0
 

Author Comment

by:AidenA
ID: 34970483
tried your query above but just got an error 'operation must use an updatable query' so it doesn't seem to like that

but just looking at that subselect, something doesn't make sense to me about it. I can't imagine how that will work without some kind of join... how does the update know where its putting the values that are returned by the subselect?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34970677
the subselect has a WHERE clause which plays the role of JOIN, see the condition: it maps the fields from the 2 table aliases X and T, one coming from the outer select/update, one from the subselect.

however, ms access indeed might not play nice with this kind of syntax... so you will eventually require the 3-step method you mentioned
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34970682
or using a view instead of the temp table ...
0
 

Author Comment

by:AidenA
ID: 34972308
ok thanks... might try the view if i have the time
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question