nested update query with inner join problem

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

AidenAAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
AidenAAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
AidenAAuthor Commented:
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
 
AidenAAuthor Commented:
sorry, missed your last post... let me just try it...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, the 2 steps (actually 3 steps to drop the table afterwards) shall work, of course :)
0
 
AidenAAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
or using a view instead of the temp table ...
0
 
AidenAAuthor Commented:
ok thanks... might try the view if i have the time
0
All Courses

From novice to tech pro — start learning today.