Re: question about update/join query

[Andy Wallace]

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]