在单个 SQL 查询中更新具有不同值的多行 [英] Update multiple rows with different values in a single SQL query

问题描述

我有一个带有表 myTable 和列 id、posX、posY 的 SQLite 数据库.行数不断变化(可能增加或减少).如果我知道每行 id 的值和行数，我是否可以执行单个 SQL 查询来更新所有 posX 和 posY 根据 id 有不同值的字段?

I have a SQLite database with table myTable and columns id, posX, posY. The number of rows changes constantly (might increase or decrease). If I know the value of id for each row, and the number of rows, can I perform a single SQL query to update all of the posX and posY fields with different values according to the id?

例如:

---------------------
myTable:

id   posX    posY

1      35     565
3      89     224
6      11     456
14     87     475
---------------------

SQL 查询伪代码:

UPDATE myTable SET posX[id] = @arrayX[id], posY[id] = @arrayY[id] "

@arrayX 和 @arrayY 是存储 posX 和 posY 字段的新值的数组.

@arrayX and @arrayY are arrays which store new values for the posX and posY fields.

例如，如果 arrayX 和 arrayY 包含以下值:

If, for example, arrayX and arrayY contain the following values:

arrayX = { 20, 30, 40, 50 }
arrayY = { 100, 200, 300, 400 }

... 那么查询后的数据库应该是这样的:

... then the database after the query should look like this:

---------------------
myTable:

id   posX    posY

1      20     100
3      30     200
6      40     300
14     50     400
---------------------

这可能吗?我现在每个查询更新一行，但随着行数的增加，它将需要数百个查询.顺便说一下，我是在 AIR 中完成所有这些工作的.

Is this possible? I'm updating one row per query right now, but it's going to take hundreds of queries as the row count increases. I'm doing all this in AIR by the way.

推荐答案

有几种方法可以体面有效地完成此任务.

There's a couple of ways to accomplish this decently efficiently.

首先-
如果可能，您可以对临时表进行某种批量插入.这在某种程度上取决于您的 RDBMS/宿主语言，但最坏的情况是这可以通过简单的动态 SQL(使用 VALUES() 子句)来完成，然后是标准的从另一个表更新.不过，大多数系统都提供用于批量加载的实用程序

First -
If possible, you can do some sort of bulk insert to a temporary table. This depends somewhat on your RDBMS/host language, but at worst this can be accomplished with a simple dynamic SQL (using a VALUES() clause), and then a standard update-from-another-table. Most systems provide utilities for bulk load, though

第二-
这也有点依赖于 RDBMS，您可以构造一个动态更新语句.在这种情况下，CTE 中的 VALUES(...) 子句已即时创建:

Second -
And this is somewhat RDBMS dependent as well, you could construct a dynamic update statement. In this case, where the VALUES(...) clause inside the CTE has been created on-the-fly:

WITH Tmp(id, px, py) AS (VALUES(id1, newsPosX1, newPosY1), 
                               (id2, newsPosX2, newPosY2),
                               ......................... ,
                               (idN, newsPosXN, newPosYN))

UPDATE TableToUpdate SET posX = (SELECT px
                                 FROM Tmp
                                 WHERE TableToUpdate.id = Tmp.id),
                         posY = (SELECT py
                                 FROM Tmp
                                 WHERE TableToUpdate.id = Tmp.id)


WHERE id IN (SELECT id
             FROM Tmp)

(根据文档，这应该是有效的 SQLite 语法，但我无法让它在小提琴中工作)

(According to the documentation, this should be valid SQLite syntax, but I can't get it to work in a fiddle)

这篇关于在单个 SQL 查询中更新具有不同值的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！