Update records in table from CTE results

Question

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

@GordonLinoff i want to Update `NIP,NAME,DEPARTMENT,STATUSIN,STATUSOUT` — Dea Ananda, Feb 17 '20 at 02:44

score 1 · Answer 1 · answered Feb 17 '20 at 02:41

1

You just use the CTE as if it were a table. Your question is rather vague on what you want updated with the CTE, but the syntax is something like:

WITH TOUPDATE AS (
      SELECT DD.*,
             RN = ROW_NUMBER() OVER (PARTITION BY NIP, NAME, DEPARTMENT ORDER BY DATEATTEND)
      FROM DAILYDATA dd
     )
UPDATE TOUPDATE
    SET something = run;

answered Feb 17 '20 at 02:41

Gordon Linoff

1,242,037
58
646
786

i want to Update `NIP,NAME,DEPARTMENT,STATUSIN,STATUSOUT` like the value in insert function – Dea Ananda Feb 17 '20 at 02:47
2

@DeaAnanda . . . I might suggest that you ask a NEW question, with appropriate sample data, desired results, and explanation of what you want to do. This answers the question you asked here. – Gordon Linoff Feb 17 '20 at 02:50

Nguyễn Văn Phong · Accepted Answer · 2020-02-17T04:05:51.357

1

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

edited Feb 17 '20 at 04:05

answered Feb 17 '20 at 02:42

Nguyễn Văn Phong

13,506
17
39
56

Thanks for helping me, `; WITH CTE AS ( SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY NIP, NAME, DEPARTMENT ORDER BY DATEATTEND) FROM DAILYDATA ) Update DAILYDATAWH SET DAILYDATAWH.NIP = CTE.NIP , DAILYDATAWH.NAME = CTE.NAME, DAILYDATAWH.DEPARTMENT = CTE.DEPARTMENT, DAILYDATAWH.STATUSIN = MIN(CTE.DATEATTEND), DAILYDATAWH.STATUSOUT = MAX(CTE.DATEATTEND) From DAILYDATA Inner join CTE on DAILYDATAWH` i've been following your answer,but get an error in `on DAILYDATAWH` – Dea Ananda Feb 17 '20 at 02:55
what is 'your_condition_join_here', can you explain that an give an example? – Dea Ananda Feb 17 '20 at 02:56
your_condition_join_here = `your_table.column1 = CTE.column1`, `column1` often is primary key. – Nguyễn Văn Phong Feb 17 '20 at 02:58
`on DAILYDATAWH.NIP = CTE.NIP` but still error in `DAILYDATAWH.NIP` – Dea Ananda Feb 17 '20 at 03:01
Capture an image full your code & error msg, please – Nguyễn Văn Phong Feb 17 '20 at 03:01
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/207955/discussion-between-phong-and-dea-ananda). – Nguyễn Văn Phong Feb 17 '20 at 03:02

score 0 · Answer 3 · edited Feb 17 '20 at 16:09

Not sure why you're calculating row_number but you might

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

Update records in table from CTE results

3 Answers3

Linked