CODEWITHSUNDEEP
sql
user85116
user85116

Reputation: 4502

update statement using nested query

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?

Upvotes: 35

Views: 103405

Answers (6)

SatyaHarish
SatyaHarish

Reputation: 41

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;

Upvotes: 4

DBGEEK
DBGEEK

Reputation: 11

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

Upvotes: 1

rajnish
rajnish

Reputation: 829

Following update statement should do what you are looking for

update mytable mt set  lastLogDate  = (select max(logDate) from  mytable where userID = mt.userID)

Upvotes: 3

DWright
DWright

Reputation: 9500

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

Upvotes: 64

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

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;

Upvotes: 12

Alf
Alf

Reputation: 1453

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

Upvotes: 4

Related Questions