Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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

0
AidenA
Asked:
AidenA
  • 5
  • 5
2 Solutions
 
Guy Hengel [angelIII / a3]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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now