Message Time= SELECT MAX(Message Time) FROM Item Log WHERE Item ID=F. Taking out the criteria so that all 2500 rows updated took 40 seconds.
I had to do a very simple Microsoft SQL query in which I wanted to update some of the values in a row from a row in the same table.
Even if the documentation says you can specify a CTE without declaring the column names, I couldn't do it in this situation, I don't know why.
I admit I only tried the CTE solution for a minute before discarding it as too verbose.
Sebastian covers a technique for this in a recent blog post: sqlity.net/en/2867/update-from-select This will tend to work across almost all DBMS which means learn once, execute everywhere. Col2 AS _Col2 FROM T1 JOIN T2 ON T1= T2/*Where clause added to exclude rows that are the same in both tables Handles NULL values correctly*/ WHERE EXISTS(SELECT T1. I know this is old, but just wanted to say this one worked for me.
If that is more important to you than performance you might prefer this answer, especially if your update is a one off to correct some data. My server wont allow FROM to be used in an UPDATE statement. This may be a niche reason to perform an update (for example, mainly used in a procedure), or may be obvious to others, but it should also be stated that you can perform an update-select statement without using join (in case the tables you're updating between have no common field).But because this query is relatively slow, I have added 2 status columns, and I want to UPDATE the columns with the calculated statuses well after the events happen. I hope the solution you are looking for can be served up using MERGE statement.