Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

nested update query with inner join problem

Posted on 2011-02-24
10
Medium Priority
?
364 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

705 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