I have test table(1) with following values

Yet I have an update of this table called table(2) :

As you can see table(2) is similar table(1) but it has new values with new dates for each ID. How should I use update command to update table1 based on new values of table2

CodePudding user response：

In sql the fastest way to do so via a merge function, it will add the new rows to the table1 that are existing in table2 and not in table1.

merge into table1 t1
using table2 t2
on t1.id = t2.id
and t1.date = t2.date
when not matched then 
insert (t1.id, t1.date, t1.value1, t1.value2 , t1.value3, t1.value4)
values (t2.id, t2.date, t2.value1, t2.value2 , t2.value3, t2.value4)

CodePudding user response：

What I understood from your question is that you have multiple records for each id in table2 and you want the values against the latest row based on the data to be updated to table1. in such a case, you can try with a ROW_NUMBER or RANK function, just like this

;WITH CTE
AS
(
    SELECT
        RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC),
        id,
        date,
        val1,
        val2,
        val3
        FROM Table2
)
UPDATE T1
    SET
        Val1 = T2.Val1
        Val2 = T2.Val2
    FROM Table1 T1  
        INNER JOIN CTE T2
            ON T1.id = T2.id
        WHERE T2.RN = 1