Update Records in Table from Cte

Update records in table from CTE

Updates you make to the CTE will be cascaded to the source table.

I have had to guess at your schema slightly, but something like this should work.

;WITH T AS
(   SELECT  InvoiceNumber, 
            DocTotal, 
            SUM(Sale + VAT) OVER(PARTITION BY InvoiceNumber) AS NewDocTotal
    FROM    PEDI_InvoiceDetail
)
UPDATE  T
SET     DocTotal = NewDocTotal

Update records in table from CTE results

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

Update statement SQL with CTE

Try this:

....
CTE2 AS ( 
   SELECT members_id
   FROM members
), CTE3 AS (
   SELECT  CTE.members_id, CTE.Credentials, CTE.CredCount,  
           CTE.members_amountdue,  
           (CTE.members_amountdue + 25) as NewPriceTotal
   FROM CTE JOIN CTE2 ON CTE.members_id = CTE2.members_id
   WHERE CTE.CredCount = 2)
UPDATE CTE3
SET members_amountdue = NewPriceTotal

Update a SQL Server table from a CTE and Case statement

Without CTE you can try

UPDATE segments
SET EMP_ID = (case when len(ltrim(rtrim(EMP_ID))) = 5 then right(('100' + EMP_ID),8 )
    when len(ltrim(rtrim(EMP_ID))) = 6 then right(('10' + EMP_ID),8 )
    when len(EMP_ID) = 7 then right('10'+(left(ltrim(EMP_ID),6)),8)
  else emp_ID
  end)

OR if you want to use CTE you can try

With CTE
As 
(
SELECT ltrim(rtrim([EMP_ID])) as empIDTrim
, Emp_ID

FROM [SAMPLE].[dbo].[segments]
)
UPDATE CTE
SET EMP_ID = (case when len(empIDTrim)= 5 
then right('100' + empIDTrim,8 )
    when len(empIDTrim)= 6 
then right('10' + empIDTrim ,8 )
    when len(empIDTrim) = 7 
  then right('10'+left(empIDTrim,6),8)
  else empIDTrim
  end) 

Can I update a cte?

You can use cte for update, e.g. (assuming that id is a primary key):

with cte as ( 
    select 
        id, 
        my_field_name, 
        row_number() over (order by gps_device_id) as rn
    from gps_devices
) 
update gps_devices
set my_field_name = (rn % 3)::text
from cte
where gps_devices.id = cte.id;

You can insert, update or delete rows of a table (view) rather than a resultset of a query.

Use CTE for update records

When both tables have columns with the same name, I don't know that you're going to be able to do that (in fact I'm surprised you didn't get dinged with an ambiguous column name error). How about:

UPDATE t2 SET t2.class = t1.class
  FROM dbo.table2 AS t2
  INNER JOIN dbo.table1 AS t1
  ON t1.Id = t2.Id
  WHERE t1.class IS NOT NULL;

• Example db<>fiddle

I'm not sure why it's so important to use a CTE, and this might be harder for future maintainers to understand why you want this roundabout approach too, but perhaps:

;WITH cteupdate AS
(
  SELECT t2.Id, t2.class, newclass = t1.class
    FROM dbo.table1 AS t1
    INNER JOIN dbo.table2 AS t2
    ON t1.Id = t2.Id
    WHERE t1.class IS NOT NULL
)
UPDATE cteupdate SET class = newclass;

The main problem (aside from ambiguous column names, which I address by applying a different alias to the "new" class column), is that you can't reference t1/t2 outside of the CTE, since all that's left at that point is the CTE.

• Example db<>fiddle

Oracle SQL update CTE table

This will only update the rows that need updating and correlating on the ROWID pseudo-column will be more efficient (than having to perform a self-join using ID):

UPDATE table_name
SET status = 'TRUE'
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT status,
           MAX( status ) OVER ( PARTITION BY id ) AS max_status
    FROM   table_name
  )
  WHERE status IS NULL OR status <> max_status
  AND   max_status = 'TRUE'
);

Which, for the sample data:

CREATE TABLE table_name ( ID, STATUS, AGE, MDI ) AS
SELECT 1, 'TRUE',  18, 89 FROM DUAL UNION ALL
SELECT 1, 'TRUE',  18, 89 FROM DUAL UNION ALL
SELECT 3, 'TRUE',  18, 89 FROM DUAL UNION ALL
SELECT 3, NULL,    19, 99 FROM DUAL UNION ALL
SELECT 4, 'TRUE',  19, 88 FROM DUAL UNION ALL
SELECT 4, 'FALSE', 18, 88 FROM DUAL UNION ALL
SELECT 5, 'TRUE',  18, 67 FROM DUAL UNION ALL
SELECT 6, 'FALSE', 18, 77 FROM DUAL;

Only updates 2 rows and updates the table to:

ID | STATUS | AGE | MDI
-: | :----- | --: | --:
 1 | TRUE   |  18 |  89
 1 | TRUE   |  18 |  89
 3 | TRUE   |  18 |  89
 3 | TRUE   |  19 |  99
 4 | TRUE   |  19 |  88
 4 | TRUE   |  18 |  88
 5 | TRUE   |  18 |  67
 6 | FALSE  |  18 |  77

db<>fiddle here

---

Related Topics