Update records in table from CTE results

I want to update the records in my table using CTE. And I’ve been trying with the insert function using CTE and it’s working. This is my query with the insert function:

; WITH CTE AS
(
    SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY NIP, NAME, DEPARTMENT ORDER BY DATEATTEND)
    FROM DAILYDATA
)
INSERT INTO DAILYDATAWH (NIP, NAME, DEPARTMENT, STATUSIN, STATUSOUT)
SELECT  NIP, NAME, DEPARTMENT, STATUSIN = MIN(DATEATTEND), STATUSOUT = MAX(DATEATTEND)
FROM    CTE
GROUP BY NIP, NAME, DEPARTMENT, (RN - 1) / 2

How to change that with the update function?

i want to change to update function because when i use insert function,the previous data that already exists in DAILYDATA appear again

This is table DAILYDATA

This is table DAILYDATAWH

Answer

It seems to me that you want to update table after joining like below

; WITH CTE AS
(
   SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY NIP, NAME, DEPARTMENT ORDER BY DATEATTEND)
   FROM   DAILYDATA
)
Update your_table
SET -- your_table.columns = CTE.columns  
From your_table
Inner join CTE on your_condition_join_here

Read the following post to have a better understanding

SQL Update after Joining Two Tables

Updated

You should use multiple CTE in a query like below

; WITH CTE AS
(
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY NIP, NAME, DEPARTMENT ORDER BY DATEATTEND)
FROM DAILYDATA
), CTE2 AS
(
SELECT NIP, NAME, DEPARTMENT, MIN(CTE.DATEATTEND) AS Min_DATEATTEND, MAX(CTE.DATEATTEND) AS Max_DATEATTEND
From CTE
GROUP BY NIP, NAME, DEPARTMENT
)
Update DAILYDATAWH
SET DAILYDATAWH.NIP = CTE2.NIP ,
DAILYDATAWH.NAME = CTE2.NAME,
DAILYDATAWH.DEPARTMENT = CTE2.DEPARTMENT,
DAILYDATAWH.STATUSIN = CTE2.Min_DATEATTEND,
DAILYDATAWH.STATUSOUT = CTE2.Max_DATEATTEND
From DAILYDATA
Inner join CTE2 on DAILYDATA.NIP = CTE2.NIP