Menu
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?
588
UPDATE operations with subqueries that reference the same table object are supported only if all of the following conditions are true: The subquery either returns a single row, or else has no correlated column references. The subquery is in the UPDATE statement WHERE clause, using Condition with Subquery syntax.
UPDATE Subquery Finally, you can use a subquery in an UPDATE statement for the table to be updated. In the previous examples, we have just used the product table. However, you can use a subquery instead of the product table, which will return a result set that can be updated.
UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition; table_name: name of the table column1: name of first , second, third column.... value1: new value for first, second, third column.... condition: condition to select the rows for which the values of columns needs to be updated.
Unfortunately, SQL does not allow more than one SET condition per query. The UPDATE statement lists the target table, the SET statement specifies the column on that target table and the SELECT statement lists the source table and column.
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 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; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
