valores - update sql ejemplo dos tablas

Actualizar registros en la tabla de CTE

Question

Tengo el siguiente CTE que me dará el DocTotal para la factura completa.

 ;WITH CTE_DocTotal
AS
(
SELECT SUM(Sale + VAT) AS DocTotal
FROM PEDI_InvoiceDetail
GROUP BY InvoiceNumber
)
UPDATE PEDI_InvoiceDetail
SET DocTotal = CTE_DocTotal.DocTotal

Ahora con este resultado quiero ingresar en la columna el valor DocTotal dentro de PEDI_InvoiceDetail.

Sé que no va a funcionar y sé que me estoy perdiendo algo, ¿qué es?

Answer 1

Las actualizaciones que realice en el CTE se transferirán a la tabla de origen.

He tenido que adivinar un poco su esquema, pero algo así debería funcionar.

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

Answer 2

No necesita un CTE para esto

UPDATE PEDI_InvoiceDetail
SET
DocTotal = v.DocTotal
FROM
PEDI_InvoiceDetail
inner join
(
SELECT InvoiceNumber, SUM(Sale + VAT) AS DocTotal
FROM PEDI_InvoiceDetail
GROUP BY InvoiceNumber
) v
ON PEDI_InvoiceDetail.InvoiceNumber = v.InvoiceNumber

Answer 3

Pruebe la siguiente consulta:

;WITH CTE_DocTotal
AS
(
SELECT SUM(Sale + VAT) AS DocTotal_1
FROM PEDI_InvoiceDetail
GROUP BY InvoiceNumber
)
UPDATE CTE_DocTotal
SET DocTotal = CTE_DocTotal.DocTotal_1

Answer 4

WITH CTE_DocTotal (DocTotal, InvoiceNumber)
AS
(
SELECT InvoiceNumber,
SUM(Sale + VAT) AS DocTotal
FROM PEDI_InvoiceDetail
GROUP BY InvoiceNumber
)
UPDATE PEDI_InvoiceDetail
SET PEDI_InvoiceDetail.DocTotal = CTE_DocTotal.DocTotal
FROM CTE_DocTotal
INNER JOIN PEDI_InvoiceDetail ON ...