是否可以在单个SQLite查询中执行SELECT和UPDATE?

hgc7kmma  于 2023-01-13  发布在  SQLite
关注(0)|答案(1)|浏览(151)

我可能没有正确地措辞的标题,但我想知道是否有可能既读取数据和更新数据在一个单一的查询(或多个查询在一个单一的 * 调用 *?)使用SQLite。
作为一个示例情况,我想SELECT数据库的当前状态,然后UPDATE状态到新的东西,所有这些都在一个原子查询中。
下面是我如何使用Postgres实现这一点:

UPDATE migrations_lock
SET is_locked = 0
WHERE index = 1 RETURNING (
  SELECT is_locked AS was_locked
  FROM migrations_lock
  WHERE index = 1
);

同样,我也可以在MySQL中使用事务实现同样的功能:

START TRANSACTION;
    SELECT is_locked AS was_locked
      FROM migrations_lock
      WHERE `index` = 1;
    UPDATE migrations_lock
      SET is_locked = 0
      WHERE `index` = 1;
COMMIT;

遗憾的是,在SQLite中运行相同的Postgres查询会返回新数据,而不是现有数据。在SQLite表单(BEGIN TRANSACTION)中运行等效的MySQL查询会返回一个关于嵌套事务的错误。在一个调用中运行SELECTUPDATE查询(没有事务)会导致SELECT查询运行,而UPDATE查询被忽略。
还有一些关于如何在此拉取请求中运行查询的附加上下文。

b91juud3

b91juud31#

SQLite自版本3.33.0起支持UPDATE FROM(更新自),请参见

  • https://www.sqlite.org/lang_update.html#update_from

另一种可能性是使用CTE(公用表表达式)驱动UPDATE,如
所有公用表表达式(普通和递归)都是通过在SELECT、INSERT、DELETE或UPDATE语句前面附加WITH子句来创建的。单个WITH子句可以指定一个或多个公用表表达式,其中一些是普通表达式,另一些是递归表达式。

使用CTE驱动更新的示例:

DROP TABLE IF EXISTS test;
CREATE TABLE IF NOT EXISTS test (datee TEXT, other_colmumns);
INSERT INTO test VALUES ('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
    ,('not the date I wanted','otherdate')
;
SELECT rowid,* FROM test; /* result 1 prior to update */

/* The CTE driven UPDATE */
WITH x AS (SELECT rowid FROM test LIMIT 10,5)
    UPDATE test SET datee = '19/12/2021' WHERE test.rowid IN (SELECT * FROM x)
;
SELECT rowid,* FROM test; /* result 2 after update */
DROP TABLE IF EXISTS test; /* clean up */
  • 结果1为

  • 结果2为

修改多重选择的CTE(2 ctes x和y):-

/* The CTE driven UPDATE */
WITH x AS (SELECT rowid FROM test LIMIT 10,5),
     y as (SELECT rowid FROM test LIMIT 1,2)
    UPDATE test SET datee = '19/12/2021' WHERE test.rowid IN (SELECT * FROM x UNION ALL SELECT * FROM y)
;
  • 结果2则为

相关问题