Update with earliest date in same SQL column-CodePudding

I need to make updates to a table where I update the Order_Date column to a customer's earliest order date. All of the records associated that that customer must be updated to that earliest date. Date is in YYYYMMDD format.

My starting table looks something like this:

CUSTOMER_ID Order_Date

        1           20170101
        1           20180101
        2           20190101 
        2           20200101
        2           20210101
        3           20170101

The updated table needs to look like this:

CUSTOMER_ID Order_Date

        1           20170101
        1           20170101
        2           20190101 
        2           20190101
        2           20190101
        3           20170101

I've been trying to figure this out with a self join. Thanks for any help!

CodePudding user response：

it looks like you can apply update....from form. For example;

UPDATE TableName SET Order_Date = T.Order_Date
FROM (
    SELECT Customer_Id, MIN(CAST(Order_Date AS DATE)) as Order_Date 
    FROM TableName
    GROUP BY Customer_Id
) AS T
WHERE Customer_Id = T.Customer_Id

Look at the query. I select customer_id and order_date with group by. Also as you can see i add an aggregate function for order_date column which is "min". It selects the earliest date.

CodePudding user response：

Perhaps something like this:

UPDATE CUSTOMER c 
SET ORDER_DATE=(SELECT MIN(CAST(ORDER_DATE as DATE))
     FROM CUSTOMER c2 
     WHERE c2.CUSTOMER_ID=c.CUSTOMER_ID)