在单个查询中更新表中的多个值。

I have a table say 'Table1' as shown below

我有一个表，名为“Table1”，如下所示

  Table1
  ---------------
  Name  | Value
  ----------------
  name1 | val1

  name2 | val2

  name3 | val3

  name4 | val4

here I need to update 'Values' field based on 'Name'

这里我需要基于Name更新'Values'字段

I have used below stored procedure to do this,

我用下面的存储过程来做这个，

CREATE PROCEDURE [dbo].[UpdateDetails] 
(
    @name1 NVARCHAR(MAX) = NULL,
    @name2 NVARCHAR(MAX) = NULL,
    @name3 NVARCHAR(MAX) = NULL,
    @name4 NVARCHAR(MAX) = NULL,
)
AS
BEGIN
    UPDATE Table1 SET Value = @name1 WHERE Name = 'name1';
    UPDATE Table1 SET Value = @name2 WHERE Name = 'name2';
    UPDATE Table1 SET Value = @name3 WHERE Name = 'name3';
    UPDATE Table1 SET Value = @name4 WHERE Name = 'name4';
END

what would be the best way to update this table?

更新这个表的最佳方式是什么?

4 个解决方案

#1

Try this code:

试试这段代码:

UPDATE Table1 
SET Value = CASE Name WHEN 'name1' THEN @name1
                      WHEN 'name2' THEN @name2
                      WHEN 'name3' THEN @name3
                      WHEN 'name4' THEN @name4                    
            END
WHERE Name in ('name1', 'name2', 'name3', 'name4');

#2

You can use a CASE statement for this. I recommend you add an ELSE statement at the bottom. The bottom part will filter so you only update rows that you are searching for.

您可以使用CASE语句。我建议您在底部添加一个ELSE语句。底部的部分将进行筛选，以便您只更新正在搜索的行。

UPDATE Table1 SET Value = 
                     CASE WHEN Name = 'name1' THEN @name1 
                          WHEN Name = 'name2' THEN @name2 
                          WHEN Name = 'name3' THEN @name3 
                          WHEN Name = 'name4' THEN @name4 
                          ELSE Value
                     END
 WHERE Name in ('name1','name2','name3','name4')

#3

You haven't explicitly said so, but usually when you do this, you don't know at coding time how many rows will be in your table, nor how many of them you want to update at any given moment. The quickest way to deal with this is to put the loop in the C#. You'll need two parameters, @name and @value, which you'll create outside the loop, then you just change their values for each iteration of the loop. So your code will look like...

你还没有明确地说过，但是通常当你这样做的时候，你不知道在编码时你的表中有多少行，也不知道在任何时候你想要更新多少行。处理这个问题的最快方法是将循环放到c#中。您将需要两个参数，@name和@value，您将在循环之外创建它们，然后您只需为循环的每次迭代更改它们的值。所以你的代码看起来像……

Dictionary<string, string> myUpdates = new Dictionary<string, string>();
myUpdates.Add("name1", "value1");
myUpdates.Add("name2", "value2");
myUpdates.Add("name3", "value3");
myUpdates.Add("name4", "value4");

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand("UPDATE Table1 set value = @value where name = @name", connection);
    var nameParam = new SqlParameter("name", System.Data.SqlDbType.NVarChar);
    var valueParam = new SqlParameter("value", System.Data.SqlDbType.NVarChar);
    command.Parameters.Add(nameParam);
    command.Parameters.Add(valueParam);
    connection.Open();
    foreach (var item in myUpdates)
    {
        try
        {
            nameParam.Value = item.Key;
            valueParam.Value = item.Value;
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

"name" and "value" are worryingly abstract names for DB columns. In general, your DB column names should be more closely tied to the "real world". They're probably just examples ?

“name”和“value”是DB列令人担忧的抽象名称。通常，您的DB列名称应该与“真实世界”更紧密地联系在一起。它们可能只是例子?

#4

-1

You can rather use a dynamic sql query. Something like below. I would take like pass value1, name1 and iterate on procedure call.

您可以使用动态sql查询。就像下面。我将使用value1, name1和iterate进行过程调用。

    CREATE PROCEDURE [dbo].[UpdateDetails] 
    (
    @l_value NVARCHAR(MAX) = NULL,
    @l_name NVARCHAR(MAX) = NULL,
    @sql NVARCHAR(MAX)
    )
    AS
    BEGIN
    SELECT @sql = 'UPDATE Table1 SET Value = [' + @l_value + '] WHERE Name =['+ @l_name + '] '

    EXEC sp_executesql @sql

    END

#1