时间戳

首页 > 解决方案 > sql update INNER JOIN中关键字'INNER'附近的语法不正确

sql - sql update INNER JOIN中关键字'INNER'附近的语法不正确

问题描述

我试图通过加入两个表来更新一些记录。但它给了我一些语法错误。

请帮我解决这个问题。

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

标签： sqlsql-serversql-server-2012

解决方案

SQL Server 中的正确语法使用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;

请注意，我还引入了表别名，因此查询更易于编写和阅读。

推荐阅读