更新重复值不正确的语法。 [英] Update on Duplicate Value incorrect syntax.

问题描述

using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"))
{
    SqlCommand CmdSql = new SqlCommand("INSERT INTO bookList (ISBN,Book,Price,Link) VALUES (@ISBN,@Book,@Price,@Link) ON DUPLICATE KEY UPDATE Price = Price", conn);
    conn.Open();
    CmdSql.Parameters.AddWithValue("@ISBN", isbnCode.Text);
    CmdSql.Parameters.AddWithValue("@Book", bookEntry.Text);
    CmdSql.Parameters.AddWithValue("@Price", priceConvert);
    CmdSql.Parameters.AddWithValue("@Link", getUrl);
    CmdSql.ExecuteNonQuery();
    conn.Close();
}

以下是我的表格：



http://i622.photobucket.com/albums/tt301/kidown/table.jpg



程序给我附近的语法错误关键字'ON'。



是因为数据值相同吗？



基本上我的数据是一样的更新价格（如果价格有差异）。



链接被设置为主键，因为它是表中唯一必须唯一的键。



谢谢

The following is the table I have:

http://i622.photobucket.com/albums/tt301/kidown/table.jpg

The program gives me "Incorrect syntax near the keyword 'ON'."

Is it because the data value the same?

basically when the data is the same I want to update the Price (if there is a difference in price).

Link is set as primary key since its the only one in the table that has to be unique.

Thanks

推荐答案

SQL Server中的正确解决方案是merge [ ^ ]。这允许你检查某些东西是否存在，并在此基础上进行更新或插入。

The correct solution in SQL Server is merge[^]. This allows you to check if something exists, and do either an update, or in insert, on that basis.

我认为

ON DUPLICATE KEY UPDATE

仅在MySQL中可用，至少它目前似乎不适用于SQLExpress。所以你必须以不同的方式去做。

is only available in MySQL, at the very least it doesn't appear to be available for SQLExpress at this time. So you'll have to do it a different way.

这篇关于更新重复值不正确的语法。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！