35

I have a table:

mytable:
    id
    userID
    logDate
    lastLogDate

For every row in that table, I want to update the 'lastLogDate' column to be the max value of logDate on a per user basis...

Conceptually, each user should have a lastLogDate = the value that is returned by:

select max(logDate) from mytable group by userID

Can somebody help me write the update statement for that?

Beth
  • 9,531
  • 1
  • 24
  • 43
user85116
  • 4,422
  • 7
  • 35
  • 33
  • 2
    How do you plan to keep that information up-to-date? Wouldn't a view / materialized view make more sense? – Frank Schmitt Dec 14 '12 at 15:33
  • The table doesn't get updated, it's created specifically for analysis... Not sure on the database yet, should be fairly standard no? Either hsqldb, postgresql or mssql... – user85116 Dec 14 '12 at 15:40

6 Answers6

63

Something like this?

UPDATE mytable SET lastLogDate = t.maxDateForUser  
FROM  
(  
    SELECT userid, MAX(logDate) as maxDateForUser  
    FROM mytable  
    GROUP BY userId  
) t  
WHERE mytable.userid = t.userid
DWright
  • 9,258
  • 4
  • 36
  • 53
11

You can do this:

UPDATE t
SET t.logDate = t2.LatestDate
FROM YourTable t
INNER JOIN
(
    SELECT userID, MAX(LogDate) LatestDate
    FROM YourTable
    GROUP BY userID
) t2 ON t.userID = t2.userID;
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • I'd go with this for an update, but if the max value of logDate per user changes, I guess that the logDate have to change as well. If so, I would use a trigger to do the job. – Danielle Paquette-Harvey Dec 14 '12 at 15:45
  • @DaniellePaquette-Harvey - Yes in case you have to user a trigger, and put this query inside it. – Mahmoud Gamal Dec 14 '12 at 15:52
  • Thanks Mahmoud, I chose DWright's answer since it is pretty much identical and he posted first. – user85116 Dec 14 '12 at 15:56
  • 1
    @user85116 - It is OK, no problem. Doesn't matter whose answer was accepted, the important thing is that, you got a helpful answers. Thats it. Glad I could help. – Mahmoud Gamal Dec 14 '12 at 15:58
4

I don’t know if I understood you correctly. Otherwise be a bit more specific, but from what I get, you should do something along the lines of:

UPDATE `mytable`
SET lastLogDate = (SELECT statement goes here)
WHERE ...
Alf
  • 1,414
  • 1
  • 15
  • 27
  • 5
    Yes, that's the basic structure I have envisioned as well, the tricky part is in the stuff that you didn't fill in :) – user85116 Dec 14 '12 at 15:42
4
UPDATE mytable mT,
  (SELECT userid,
          MAX(logDate) AS maxDateForUser
   FROM mytable
   GROUP BY userId) t
SET mT.lastLogDate = t.maxDateForUser
WHERE mT.userid = t.userid;
Pallav Jha
  • 3,409
  • 3
  • 29
  • 52
SatyaHarish
  • 41
  • 1
3

Following update statement should do what you are looking for

update mytable mt set  lastLogDate  = (select max(logDate) from  mytable where userID = mt.userID)
rajnish
  • 809
  • 8
  • 5
1

you can simply write a nested query like this


    Update  mytable a 
    set 
    a.lastLogDate = (select max(logDate) from mytable b
    where a.id=b.id)
    Where...;
DBGEEK
  • 11
  • 1