使用不同的值更新列中的多个表行

I have multiple rows i need to update and insert customer names. How can I do this in one query as opposed to running something like the below query again and again with different names?

我有多行需要更新并插入客户名称。我如何在一个查询中执行此操作,而不是使用不同的名称一次又一次地运行类似下面的查询?

UPDATE orders
SET cust_name = 'Sue'
WHERE p_id = 6

2 个解决方案

#1

You can use something like

你可以使用类似的东西

    UPDATE mytable
    SET myfield = CASE other_field
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
    WHERE id IN (1,2,3)

For more info check Update Multiple Rows With Different Values and a Single SQL Query

有关更多信息,请检查使用不同值和单个SQL查询更新多行

#2

You generally can't. At least, MySQL, DB2, SQL Server and PostgreSQL don't support queries where different WHERE clauses apply to different SET clauses.

你一般不能。至少,MySQL,DB2,SQL Server和PostgreSQL不支持不同WHERE子句适用于不同SET子句的查询。

You also don't need to. What you usually can do is use a prepared statement with placeholders for the variables, that you execute with a list of tuples of parameters. Depending on the language, database adapter and database, this may be executed in 'batch' mode, which is quite efficient.

你也不需要。您通常可以使用带有占位符的预准备语句来为变量执行,并使用参数元组列表执行。根据语言,数据库适配器和数据库,这可以在“批处理”模式下执行,这非常有效。

More precision requires more information about the language, database and database adapter you are using.

更高精度需要有关您正在使用的语言,数据库和数据库适配器的更多信息。

#1