I want to put only the max date into the field... I was thinking that max
was a group function, but now that I type that out loud, perhaps I'm not
using all the neurons available... hmmm...
thanks,
andy
Martin wrote:
Hi Andy-
Is there a reason why you are using Query group by clause in UPDATE
statement?
M
----- Original Message ----- From: "Andy Wallace" <[EMAIL PROTECTED]>
To: "mysql list" <mysql@lists.mysql.com>
Sent: Wednesday, May 07, 2008 1:07 PM
Subject: Re: question about update/join query
Clarification: I DON'T want to update the last_visit field if there
is no matching event record...
I managed to get this to sort of work:
update enduser E
set E.last_visit = (select MAX(EL.event_time)
from event_log EL
where EL.enduser_acnt = E.enduser_acnt
group by EL.enduser_acnt);
but it updated the last_visit field to the default value if it found
no matching event_log row... which I don't want to happen.
thanks,
andy
Andy Wallace wrote:
Hey all -
I have two tables - an event_log table, and a user table. There is
a "last_visit" column in the user table, and I want to update it from
the event_log with the most recent event timestamp. And I want to do
it without a subquery, eventually, both these tables will be pretty
large, especially the event_log.
I tried this:
update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt
set E.last_visit = MAX(EL.event_time)
group by EL.enduser_acnt
but I get an error on the group by. The pertinent tables sections are:
table event_log
event_time TIMESTAMP
enduser_acnt int
table enduser
enduser_acnt int
last_visit datetime
Any help appreciated. Thanks...
andy
--
Andy Wallace - CISData - IDX Slave
AIM: acmwallace [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Andy Wallace - CISData - IDX Slave
AIM: acmwallace [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
