473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Nested SQL Update Query NOT via INNER JOIN

3
I need to post the number of users to the group table.

I’m remembering that this is NOT how you do it (query not updatable):
Expand|Select|Wrap|Line Numbers
  1. UPDATE TBL_GROUP
  2.    INNER JOIN
  3.          (SELECT TBL_GROUP_USER.Group_I,
  4.              Count(TBL_GROUP_USER.User_I) AS User_Count_N 
  5.           FROM TBL_GROUP_USER
  6.           GROUP BY TBL_GROUP_USER.User_I) AS USER_COUNT 
  7.       ON TBL_GROUP.Group_I = USER_COUNT.Group_I
  8.    SET TBL_GROUP.User_Count_N = 
  9.       [USER_COUNT].[User_Count_N ];
But I’m blanking on how to do this. Am I supposed to be putting the subquery in the WHERE statement of the main query?
Thank you!
Jan 23 '13 #1
6 2114
Rabbit
12,516 Expert Mod 8TB
Storing the count breaks normalization rules. You should just calculate the count when needed.
Jan 23 '13 #2
qryHmm
3
I agree. So I should give more background as to why I'm doing this. :)

I don't ACTUALLY need the count, I need to cycle through the user list and pull all the user names to export to excel.

I have a temp table with all the groups (pulled from the group . I need to fill in the user names in one field seperated by ";"s.

Originally, I opened the group table got the id, opened the user table (filtered by group id) and read all the users. My complaint with that was that it was taking too long (more than 30 seconds) (and I had to do it twice, and I want to use the OutputTo option to export it with formatting. The whole thing takes about 2 minutes). Realizing that some groups didn't have users and some groups only had one user, I thought I would update the count first to my temp table so that when I cycled though the user names, I would only doing it on groups where the user count is >1.

Does that make sense? I'm trying to avoid the time of looping through records that are going to have 0 results (which then don't need to be updated) or 1 result (which can be updated more quickly via a SQL query).

But, even were it not for the looping of user names, I've started making temp tables most of the time before I export to excel, so I could see my needing this even if I only needed to export the count alone. I just can't see how I don't know how to do this. Maybe in the past I would have linked the nested query as a select query to the append query that made the temp table. That should work, but I'm still curious how to do correctly write a nested udpate query.
Thanks a ton. I'm open to other suggestions.
Jan 23 '13 #3
Rabbit
12,516 Expert Mod 8TB
Why are you opening the user table and filtering down to a group? Are you manually stringing together the values? You can automate that with code.

As for the count, it's unlikely you'll be able to create an updateable query that is joined to an aggregate. You should just create the temp table with the count in the first place.
Jan 23 '13 #4
qryHmm
3
I have it automated with code. I set the first recordset to show the group id from the temp table, .movefirst, pull the group id, set the second recordset to the user+group table, filtered by that group id to return only those 0-4 (typically users), .movefirst into the user table, pull the user names, loop to string them together until .eof, write the sting to a field in the temp table. Then export. 2 minutes. I would like to make it faster by not looping through ALL records in the temp group table, just the ones that are going to return results. That's why I want to add a count to the temp table, so that I can set the recordset not to the entire table, but instead to the temp table filtered where the count is >1. Each cycle through the entire temp group table (and subsequent loop through the user group table is taking 50 seconds. Whereas my SQL queries are taking 1 second).

Regarding the update with a nested query. I'll stop thinking about it if it's not possible. I'll join the nested query to the append query that made the temp table in the first place.
Thanks!!
Jan 23 '13 #5
Rabbit
12,516 Expert Mod 8TB
Rather than resetting the recordset each time, you could order the recordset by the group.

As far as leaving out the ones with 1 or 0 records, you could set the recordset to this:
Expand|Select|Wrap|Line Numbers
  1. SELECT u.*
  2. FROM TBL_GROUP_USER u
  3. INNER JOIN (
  4.    SELECT Group_I
  5.    FROM TBL_GROUP_USER 
  6.    GROUP BY User_I
  7.    HAVING Count(*) > 1
  8. ) t ON u.Group_I = t.Group_I
  9. ORDER BY u.Group_I
Jan 23 '13 #6
NeoPa
32,556 Expert Mod 16PB
Something like the following may be what you're after :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tGU.*
  2. FROM     TBL_GROUP_USER AS tGU
  3. WHERE    (tGU.Group_I In(
  4.     SELECT   tGU.Group_I
  5.     FROM     TBL_GROUP_USER AS tGU
  6.     GROUP BY tGU.Group_I
  7.     HAVING   Count(*)>1)
  8. ORDER BY tGU.Group_I
I confess it was Rabbit that introduced me to the concept of using SubQueries outside of the FROM clause.

Subqueries in SQL may help too.

PS. Cool Bytes ID for Access = qryHmm
Jan 23 '13 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

23
by: Brian | last post by:
Hello All - I am wondering if anyone has any thoughts on which is better from a performance perspective: a nested Select statement or an Inner Join. For example, I could do either of the...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
10
by: deko | last post by:
I've tried each of the below, but no luck. UPDATE tblEntity As tbl INNER JOIN search3220 As qry ON tbl.Entity_ID = qry.Entity_ID SET tbl.Cat_ID = 289; UPDATE tblEntity INNER JOIN search3220 ON...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
5
by: teddysnips | last post by:
Having upsized my client's back-end DB to SQL Server, the following query does not work ("Operation must use an updateable query"). UPDATE tblbookings INNER JOIN tblREFUNDS ON...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.