CODEWITHSUNDEEP
sqlsql-server
Dea Ananda
Dea Ananda

Reputation: 115

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

TABLE DAILYDATA

This is table DAILYDATAWH

TABLE DAILYDATAWH

error

Upvotes: 0

Views: 1083

Answers (3)

Sepehr Kazemi
Sepehr Kazemi

Reputation: 150

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

Upvotes: 0

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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;

Upvotes: 1

Related Questions