Actualizar la columna en la tabla de Facturas con el total de pago de la tabla de Pagos.
Tengo 2 tablas (mostradas a continuación) – Invoice y Payments. Una sola factura puede tener múltiples pagos. Necesito SUMAR el total de PayAmt de Payments y actualizar la columna PayTotal en la tabla Invoice.
Esto NO está funcionando:
UPDATE Invoices
SET Invoices.PayTotal = Payments.Total
FROM Invoices
INNER JOIN
(SELECT InvNum_FK, SUM(PayAmt) as Total
FROM Payments) ON Invoices.InvNum_PK = Payments.InvNum_FK
CREATE TABLE dbo.Invoices
(
InvNum_PK nvarchar(255) PRIMARY KEY,
InvAmt money,
InvDate date,
CustName nvarchar(255),
PayTotal money,
PayCount int
);
CREATE TABLE dbo.Payments
(
PayNumPK int PRIMARY KEY,
InvNumFK nvarchar(255)
FOREIGN KEY REFERENCES dbo.Invoices(InvNum_PK),
PayAmt money,
PayDate date,
);
INSERT INTO Invoices
VALUES ('GLI101', 838.93, '2021-08-01', 'George Washington', 0, 0);
INSERT INTO Invoices
VALUES ('GLI202', 1280.26, '2021-08-02', 'Abe Lincoln', 0, 0);
INSERT INTO Invoices
VALUES ('GLI303', 1456.23, '2021-08-03', 'Tom Jefferson', 0, 0);
INSERT INTO Invoices
VALUES ('GLI404', 1124.97, '2021-08-04', 'Jim Madison', 0, 0);
INSERT INTO Payments VALUES (1, 'GLI101', 223.33, '08/15/2021')
INSERT INTO Payments VALUES (2, 'GLI101', 211.88, '09/16/2021')
INSERT INTO Payments VALUES (3, 'GLI101', 316.44, '09/14/2021')
INSERT INTO Payments VALUES (4, 'GLI404', 415.46, '09/10/2021')
INSERT INTO Payments VALUES (5, 'GLI404', 115.46, '09/04/2021')
davy.ai
La consulta correcta para actualizar la columna
PayTotal
en la tabla deInvoice
es:UPDATE Invoices
SET PayTotal = Payments.Total
FROM Invoices
INNER JOIN
(SELECT InvNum_FK, SUM(PayAmt) as Total
FROM Payments
GROUP BY InvNum_FK) Payments ON Invoices.InvNum_PK = Payments.InvNum_FK