What is the problem with the logic in my UPDATE statement?
I would appreciate some help with an UPDATE statement.
I want to update tblOrderHead
with the content from tblCustomer
where the intDocumentNo
corresponds to the parameter @intDocumentNo
. But when I run the my statement, the order table is only updated with the content from the first row of the customer table.
What is the problem with my logic?
I use Microsoft SQL Server.
Thanks,
Stefan
UPDATE dbo.tblOrderHead
SET dbo.tblOrderHead.intCustomerNo = @intCustomerNo ,
dbo.tblOrderHead.intPaymentCode = dbo.tblCustomer.intPaymentCode,
dbo.tblOrderHead.txtDeliveryCode = dbo.tblCustomer.txtDeliveryCode,
dbo.tblOrderHead.txtRegionCode = dbo.tblCustomer.txtRegionCode,
dbo.tblOrderHead.txtCurrencyCode = dbo.tblCustomer.txtCurrencyCode,
dbo.tblOrderHead.t开发者_JS百科xtLanguageCode = dbo.tblCustomer.txtLanguageCode
FROM dbo.tblOrderHead
INNER JOIN dbo.tblCustomer ON dbo.tblOrderHead.intOrderNo = @intDocumentNo
Solution
If anyone as stupid as me out there thing the same thing, this is how you solve it:
UPDATE dbo.tblOrderHead
SET dbo.tblOrderHead.intCustomerNo = @intCustomerNo ,
dbo.tblOrderHead.intPaymentCode = dbo.tblCustomer.intPaymentCode,
dbo.tblOrderHead.txtDeliveryCode = dbo.tblCustomer.txtDeliveryCode,
dbo.tblOrderHead.txtRegionCode = dbo.tblCustomer.txtRegionCode,
dbo.tblOrderHead.txtCurrencyCode = dbo.tblCustomer.txtCurrencyCode,
dbo.tblOrderHead.txtLanguageCode = dbo.tblCustomer.txtLanguageCode
FROM dbo.tblOrderHead
INNER JOIN dbo.tblCustomer ON dbo.tblOrderHead.intCustomerNo = dbo.tblCustomer.intCustomerNo
AND dbo.tblOrderHead.intOrderNo = @intDocumentNo
Problem is that, you are not specifying the condition on which the 2 tables tblOrderHead
, tblCustomer
need to be joined!
you need something like
INNER JOIN dbo.tblCustomer
ON dbo.tblOrderHead.someColumn = dbo.tblCustomer.someColumn
and dbo.tblOrderHead.intOrderNo = @intDocumentNo
精彩评论