Using Group By in Update Query

[Jonny]

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]

it can be.
create a Group by query and linked the query on the table that you want to update.

 

[Jonny]

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"

 

[The_Doc_Man]

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]

Hi. So, rather than a GROUP BY (Totals) query, try using DMax() in your UPDATE query. Hope it helps...

 

[Jonny]

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!

UPDATE tbl SET myDate1= #12/12/2012#
WHERE myDate2=DMax("myDate2","tbl","ID=111");

Thanks!

 

[theDBguy]

I like this solution, works smoothly for me.. here is it!

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]

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]

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!

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]

Thank you for challenging me, I did it this way and that works too!

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