Solved

nested update query with inner join problem

Posted on 2011-02-24
10
355 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
  • 5
  • 5
10 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:AidenA
Comment Utility
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
Comment Utility
sorry, missed your last post... let me just try it...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 142

Expert Comment

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

Author Comment

by:AidenA
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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 142

Assisted Solution

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

Author Comment

by:AidenA
Comment Utility
ok thanks... might try the view if i have the time
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now