I tried to update some records by joining two tables. but It gives me some syntax errors.
Please help me to fix this.
x
UPDATE [GCPurchaseInstalment]
INNER JOIN @Temptable
ON GCPurchaseInstalment.StripeInvoiceId = @Temptable.InID
SET GCPurchaseInstalment.AmountReceived = @AMOUNT_RECEIVED,
GCPurchaseInstalment.PaymentFee = @PAYMENT_FEE,
GCPurchaseInstalment.ReceivedDate = @Temptable.paidDate,
GCPurchaseInstalment.StripeInvoiceId = @Temptable.InID,
GCPurchaseInstalment.IsActive = 0
WHERE GCPurchaseInstalment.GCPurchaseInstalmentId = @GCPurchaseInstalmentId
AND @Temptable.id = @RowCount
Advertisement
Answer
The correct syntax in SQL Server uses FROM
:
UPDATE pi
SET pi.AmountReceived = @AMOUNT_RECEIVED,
pi.PaymentFee = @PAYMENT_FEE,
pi.ReceivedDate = t.paidDate,
pi.StripeInvoiceId = t.InID,
pi.IsActive = 0
FROM [GCPurchaseInstalment] pi JOIN
@Temptable t
ON pi.StripeInvoiceId = t.InID
WHERE pi.GCPurchaseInstalmentId = @GCPurchaseInstalmentId AND
t.id = @RowCount;
Note that I’ve also introduced table aliases so the query is easier to write and to read.