使用ASP.NET第2部分时，关键字'join'附近的语法不正确

Question

string queryGuitarItems = "UPDATE stringInstrumentItem JOIN brand ON stringInstrumentItem.brandId = brand.brandId SET stringInstrumentItem.brandId = @brandId IN (SELECT brand.brandId FROM brand WHERE name = @oldBrandName";
    using (SqlConnection connectionGuitarItems = new SqlConnection(ConfigurationManager.ConnectionStrings["musicStoreConnection"].ToString()))
    {
        using (SqlCommand commandGuitarItems = new SqlCommand(queryGuitarItems, connectionGuitarItems))
        {
            List<SqlParameter> parameterGuitarItems = new List<SqlParameter>();
            parameterGuitarItems.Add(new SqlParameter("@brandId", newName.Text));
            parameterGuitarItems.Add(new SqlParameter("@oldBrandName", oldName));

            connectionGuitarItems.Open();
            GetParameterGuitarItems(commandGuitarItems,parameterGuitarItems.ToArray());
            commandGuitarItems.ExecuteNonQuery();
            connectionGuitarItems.Close();
            commandGuitarItems.Parameters.Clear();
        }
    }

public void GetParameterGuitarItems(SqlCommand command, params SqlParameter[] parameterGuitarItems)
{
    if (parameterGuitarItems != null && parameterGuitarItems.Any())
    {
        command.Parameters.AddRange(parameterGuitarItems);
    }
}

布兰迪德    名称     图片
1                 XYZ.jpg
2                 ABC.jpg
3             吉布森       HJK.jpg

主要目标是通过我的GridView更新表brand中名为name的列。例如，在Brandid#1中，name等于Ibanez，我想把它改为Jackson。它应该能够在没有异常错误的情况下工作。这个怎么查询？

Answer 1

我猜您使用的是SQL Server（与C#标记有关）。如果是，正确的语法是：

UPDATE sii
    SET brandId = @brandId
    FROM stringInstrumentItem sii JOIN
         brand b
         ON sii.brandId = b.brandId
    WHERE b.brandId IN (SELECT b2.brandId FROM brand b2 WHERE b2.name = @oldBrandName);

如果我理解你想做什么，那么这就太复杂了，这就足够了：

UPDATE sii
    SET brandId = @brandId
    FROM stringInstrumentItem sii 
    WHERE sii.brandId IN (SELECT b.brandId FROM brand b WHERE b.name = @oldBrandName);

或者，在应该在任何数据库中工作的版本中：

UPDATE stringInstrumentItem
    SET brandId = @brandId
    WHERE brandId IN (SELECT b.brandId FROM brand b WHERE b.name = @oldBrandName);