Using Group By in Update Query (1 Viewer)

Jonny

Registered User.
Local time
Today, 17:50
Joined
Aug 12, 2005
Messages
144
Is there way to use Group By in Update Query?
Certain entity could be getting number of dates, I wish to be able to update the latest date.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:50
Joined
May 7, 2009
Messages
19,175
it can be.
create a Group by query and linked the query on the table that you want to update.
 

Jonny

Registered User.
Local time
Today, 17:50
Joined
Aug 12, 2005
Messages
144
it can be.
create a Group by query and linked the query on the table that you want to update.
getting "operation must use an updateable query" :confused:
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:50
Joined
Feb 28, 2001
Messages
27,005
If there is an aggregate function involved in the GROUP BY query then that would make it impossible to update. In fact, per Allen Browne (see link below), the mere presence of the GROUP BY clause makes it no updateable. But here is my question: Why not just use ORDER BY instead? If you were not intending to form an aggregate function over the group, then there is no major difference between the two. If the field is "groupable" then it is also sortable and will bring all the records with the same field value together but individually.

http://allenbrowne.com/ser-61.html
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:50
Joined
Oct 29, 2018
Messages
21,360
Hi. So, rather than a GROUP BY (Totals) query, try using DMax() in your UPDATE query. Hope it helps...
 

Jonny

Registered User.
Local time
Today, 17:50
Joined
Aug 12, 2005
Messages
144
Hi. So, rather than a GROUP BY (Totals) query, try using DMax() in your UPDATE query. Hope it helps...
I like this solution, works smoothly for me.. here is it!
Code:
UPDATE tbl SET myDate1= #12/12/2012#
WHERE myDate2=DMax("myDate2","tbl","ID=111");
Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:50
Joined
Oct 29, 2018
Messages
21,360
I like this solution, works smoothly for me.. here is it!
Code:
UPDATE tbl SET myDate1= #12/12/2012#
WHERE myDate2=DMax("myDate2","tbl","ID=111");
Thanks!

Hi. You're welcome! Glad to hear you got it to work. Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:50
Joined
Feb 19, 2002
Messages
42,989
Apparently no one wanted to mention the problem with storing calculated values.

Rather than storing a value from another table, the best solution is to get it when you need it. That way you won't run into data anomalies when the date changes but your update query hasn't run.

Please do some reading on normalization to gain an understanding of the issues.
 

Jonny

Registered User.
Local time
Today, 17:50
Joined
Aug 12, 2005
Messages
144
Apparently no one wanted to mention the problem with storing calculated values.

Rather than storing a value from another table, the best solution is to get it when you need it. That way you won't run into data anomalies when the date changes but your update query hasn't run.

Please do some reading on normalization to gain an understanding of the issues.
Thank you for challenging me, I did it this way and that works too!
Code:
UPDATE tbl1 SET tbl1.myDate1 = #12/12/2012#
WHERE tbl1.myDate2 =(SELECT Max(tbl1.myDate2) AS MaxOfmyDate2
FROM tbl2 INNER JOIN tbl1 ON tbl2.ID = tbl1.ID
GROUP BY tbl2.EmpID
HAVING tbl2.EmpID="xxx");
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:50
Joined
Oct 29, 2018
Messages
21,360
Thank you for challenging me, I did it this way and that works too!
Code:
UPDATE tbl1 SET tbl1.myDate1 = #12/12/2012#
WHERE tbl1.myDate2 =(SELECT Max(tbl1.myDate2) AS MaxOfmyDate2
FROM tbl2 INNER JOIN tbl1 ON tbl2.ID = tbl1.ID
GROUP BY tbl2.EmpID
HAVING tbl2.EmpID="xxx");
Hi. Thanks for the update. If you did a speed comparison between the two approaches, which one executes faster and by how much? Just curious...
 

Users who are viewing this thread

Top Bottom