How Can I Update Table Records Using Values Calculated in a Common Table Expression (CTE)?

Update records using common expressions (CTE)

When using CTE to update table records, be sure to pay attention to the nature of CTE. CTEs are temporary collections that exist only during the query for which they are defined. Therefore, any changes made to the CTE will not directly affect the source table.

To overcome this limitation, a different approach is required to update records based on CTE. An efficient approach is to create a subquery in the CTE to calculate the required value. This subquery can then be used in an UPDATE statement to apply the calculated value to the target table.

Consider the following example where you want to update the PEDI_InvoiceDetail column in the DocTotal table based on the value calculated in the CTE:

<code class="language-sql">;WITH CTE_DocTotal AS
(
  SELECT InvoiceNumber, SUM(Sale + VAT) AS DocTotal
  FROM PEDI_InvoiceDetail
  GROUP BY InvoiceNumber
)</code>

This CTE computes the InvoiceNumber for each DocTotal and stores the result in a temporary collection. To update the PEDI_InvoiceDetail table with these values, you can use a subquery in the CTE:

<code class="language-sql">;WITH T AS
(
  SELECT InvoiceNumber,
          SUM(Sale + VAT) OVER(PARTITION BY InvoiceNumber) AS NewDocTotal
  FROM PEDI_InvoiceDetail
)
UPDATE T
SET DocTotal = NewDocTotal</code>

In this subquery, the OVER function with the SUM() clause computes a new InvoiceNumber for each partition (group) defined by DocTotal. This subquery is then aliased to T and used in an UPDATE statement to update the DocTotal column with the calculated value.

With this approach, you can effectively update the record in the PEDI_InvoiceDetail table based on the value calculated in the CTE, ensuring that the changes are persisted in the source table.

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