msdn 主题中的此 UPDATE 表语句是否正确

Question

我在以下 msdn 主题中看到了这种类型的 UPDATE 语句（就像插入语句一样）：

http: //msdn.microsoft.com/en-us/library/aa0416cz.aspx#Y2461

更新声明：-

adapter.UpdateCommand = New SqlCommand("UPDATE Customers " &
  "(CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) " & _
  "WHERE CustomerID = @oldCustomerID AND CompanyName = " &
  "@oldCompanyName", connection)

此声明正确与否？

我尝试执行它，但它给出了语法错误。

Answer 1

不，它应该是：

UPDATE Customers
SET 
CustomerID = @CustomerID,
CompanyName = @CompanyName
WHERE
CustomerID = @oldCustomerID AND
CompanyName = @oldCompanyName

或者为了完成您的示例代码，它应该是：

adapter.UpdateCommand = New SqlCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName", connection)

这是为您和这种情况提供的另一个参考： http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx

Answer 2

该 SQL 对于 INSERT INTO 似乎是正确的，但对于 UPDATE 则不是正确的 它应该是这样的：

adapter.UpdateCommand = New SqlCommand("UPDATE Customers" & _
    " SET CustomerID = @CustomerID, CompanyName = @CompanyName)" & _
    " WHERE CustomerID = @oldCustomerID AND CompanyName =" & _
    " @oldCompanyName", connection)

That SQL 就是所谓的参数化，因此这使得这段代码（在片段）非常重要：

adapter.UpdateCommand.Parameters.Add( _
  "@CustomerID", SqlDbType.NChar, 5, "CustomerID")
adapter.UpdateCommand.Parameters.Add( _
  "@CompanyName", SqlDbType.NVarChar, 30, "CompanyName")

' Pass the original values to the WHERE clause parameters.
Dim parameter As SqlParameter = dataSet.UpdateCommand.Parameters.Add( _
  "@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")
parameter.SourceVersion = DataRowVersion.Original
parameter = adapter.UpdateCommand.Parameters.Add( _
  "@oldCompanyName", SqlDbType.NVarChar, 30, "CompanyName")
parameter.SourceVersion = DataRowVersion.Original

Answer 3

据我所知，语法无效。以下给出'('附近的语法不正确。

我建议按照丹的回答。

CREATE TABLE Customers
(
CustomerID INT, 
CompanyName VARCHAR(10)
)

DECLARE 
@CustomerID INT, 
@CompanyName VARCHAR(10),
@oldCustomerID INT,
@oldCompanyName VARCHAR(10)

UPDATE Customers  (CustomerID, CompanyName)
VALUES(@CustomerID, @CompanyName)
WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName