Brent -
thanks, now I understand. My aversion to subqueries is for performance
against a very large table, which event_log promises to be. I hope to
minimize this with some time boundaries on that table (where event_time
between x and y).
But thanks, I'll play with that. And thanks Martin for the start.
andy
Brent Baisley wrote:
You can do it in a single UPDATE statement, but you do need a form of
a subquery. Why the aversion of a subquery? The simplest approach is
to first get what you want using a SELECT statement. Then change
SELECT to UPDATE and add your SET statement.
In your case there is a little twist because you have to use a group
by to get the max. So first get the users and latest event time, as
Martin stated.
SELECT user_id, MAX(event_time) maxtime FROM event_log GROUP by user_id
That can be used to create a "virtual" table you can join against for
your update statement.
UPDATE user JOIN
(SELECT user_id, MAX(event_time) maxtime FROM event_log GROUP by
user_id) AS eMax
ON user.user_id=eMax.user_id
SET last_visit=maxtime WHERE user.user_id=eMax.user_id
That should do it, although I don't think you need the WHERE clause.
That will do a full table scan on the event_log table, which can be
very bad if it is large. You can work around this by compiling groups
of users at a time. This will require querying the users and for the
group of users and joining on the event_log table.
Brent Baisley
Systems Architect
On Thu, May 8, 2008 at 12:26 AM, Andy Wallace <[EMAIL PROTECTED]> wrote:
Ok, I think I need to try to restate my problem.
I have an event_log table, which tracks events (!). Basic structure
is:
table: event_log
event_time timestamp
event_id int
user_id int
and my user table:
table: user
user_id int
name varchar(50)
last_visit datetime
I want to run a query that updates the last_visit column of user with
the MAX(event_time) row for which the user_id's match, but only if I
find an event:
update user U
set U.last_visit = (select max(L.event_time) from event_log L
where L.user_id = U.user_id)
I would like to do it without a subquery, I thought that the
multiple table syntax for UPDATE would do it, but I can't wrap
my head around it.
UPDATE user U, event_log L
SET U.last_visit = MAX(L.event_time)
WHERE U.user_id = L.user_id
GROUP BY L.event_time
I guess the main question is - CAN I do this? Or will I have to resort
to either a subquery, or external processing?
thanks,
andy
Martin wrote:
Hi Andy-
the MAX function needs group by for the column for which it calculating
max value as in this example
(select MAX(EL.event_time)
// from event_log EL
// where EL.enduser_acnt = E.enduser_acnt
//Inner join forces selection on columns which contain non null values as
seen here
from event_log AS EL INNER JOIN Event AS E
ON EL.enduser_acnt = Event.enduser_anct
group by EL.event_time);
// group by EL.enduser_acnt);
HTH
Martin
----- Original Message ----- From: "Andy Wallace" <[EMAIL PROTECTED]>
To: "Martin" <[EMAIL PROTECTED]>
Cc: "mysql list" <mysql@lists.mysql.com>
Sent: Wednesday, May 07, 2008 6:21 PM
Subject: Re: question about update/join query
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]
--
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]
