How Can I Update Table Records Using a CTE in SQL?

Use CTE to update table records

In SQL, you can use common table expressions (CTE) and UPDATE statements to modify data in a table. A CTE is an alias or temporary result set that can be referenced in queries.

To update records in a table using CTE, you can use a query like this:

WITH CTE_DocTotal AS (
  SELECT SUM(Sale + VAT) AS DocTotal
  FROM PEDI_InvoiceDetail
  GROUP BY InvoiceNumber
)

UPDATE PEDI_InvoiceDetail
SET DocTotal = CTE_DocTotal.DocTotal;

This query will update the value of the DocTotal column in the PEDI_InvoiceDetail table with the value of the DocTotal column in the CTE_DocTotal CTE.

However, in your case, the CTE definition is missing a link between the CTE and PEDI_InvoiceDetail tables. To establish this link, you add a partitioning clause to the CTE as follows:

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

UPDATE T
SET DocTotal = NewDocTotal;

This modified query will create a new CTE named T that contains the InvoiceNumber column from the PEDI_InvoiceDetail table. This allows partitioning of CTEs by InvoiceNumber, ensuring that the NewDocTotal value is calculated correctly for each invoice.

The UPDATE statement then applies the calculated NewDocTotal value to the DocTotal column in the PEDI_InvoiceDetail table.

The above is the detailed content of How Can I Update Table Records Using a CTE in SQL?. For more information, please follow other related articles on the PHP Chinese website!