update with the latest date

问题:

I have a table as shown below:

I need to update them into the latest last_order_date, which the table can be shown as below:

I have 20000 plus records, I need a query to update them at once.

Thank you for spending your time to look at it.

回答1:

Using join on max calculating subquery

UPDATE t
SET t.last_order_date =a.maxDate
FROM tableName t
INNER JOIN
 ( SELECT cust_id ,MAX(last_order_date) As maxDate
   FROM tableName GROUP BY cust_id ) a
ON a.cust_id =t.cust_id 

回答2:

This should work for you:

UPDATE [table_name] 
SET    last_order_date = (SELECT Max([b].last_order_date) 
                          FROM   [table_name] [b] 
                          WHERE  [b].cust_id = [table_name].cust_id); 

回答3:

You could calculate the maximum dates in a CTE using a window MAX(), then reference the CTE in the main (UPDATE) statement:

WITH maxdates AS (
  SELECT
    last_order_date,
    actual_last_order_date = MAX(last_order_date) OVER (PARTITION BY cust_id)
  FROM atable
)
UPDATE maxdates
SET last_order_date = actual_last_order_date
;

However, duplicating this piece of information like this doesn't seem to make much sense. You should probably consider storing last_order_date in a table where cust_id is the primary key (probably some customers table). Or even abandon storing it in a table and calculate it dynamically every time: 20,000 rows isn't really that much. (Unless you have serious expectations for that number to grow rapidly and soon.)