这个问题反映了我的问题。如何在SQLite中执行此操作?
我已经尝试UPDATE
自连接,在子查询、触发器和类似的东西中隔离自连接。下面是一个示例:
UPDATE stage
SET title =
(
SELECT
prior.title
FROM
stage prior,
stage now
WHERE
prior.rownum+1 = now.rownum
)
WHERE
title is null
SQLite 中的每个表都有一个名为 rowid 的伪列(可以使用几个不同的名称访问:rowid
、oid
、_rowid_
,除非这些名称被分配给其他实数列(。rowid 列本质上是唯一的行标识符,您可以将其用作排序条件和/或条件。
以下查询演示了如何在 rowid 的帮助下解决您的问题:
UPDATE stage
SET title = (
SELECT title
FROM stage AS prev
WHERE title IS NOT NULL AND prev.rowid < stage.rowid
ORDER BY prev.rowid DESC
LIMIT 1
)
WHERE title IS NULL
这是一个关于SQL Fiddle的演示。
您可以在此SQLite手册中阅读有关rowid的更多信息。
我为您提到的问题提出了一个解决方案(我在SQL2008,SQLite3和Oracle11g上成功测试(。我在下面复制了该解决方案:
CREATE TABLE test(mysequence INT, mynumber INT);
INSERT INTO test VALUES(1, 3);
INSERT INTO test VALUES(2, NULL);
INSERT INTO test VALUES(3, 5);
INSERT INTO test VALUES(4, NULL);
INSERT INTO test VALUES(5, NULL);
INSERT INTO test VALUES(6, 2);
SELECT t1.mysequence, t1.mynumber AS ORIGINAL
, (
SELECT t2.mynumber
FROM test t2
WHERE t2.mysequence = (
SELECT MAX(t3.mysequence)
FROM test t3
WHERE t3.mysequence <= t1.mysequence
AND mynumber IS NOT NULL
)
) AS CALCULATED
FROM test t1;
-- below here it was only tested in SQLite3, but I believe it should
-- work on other DBMS since it uses standard/non-proprietary SQL
UPDATE test
SET mynumber = (
SELECT t2.mynumber
FROM test t2
WHERE t2.mysequence = (
SELECT MAX(t3.mysequence)
FROM test t3
WHERE t3.mysequence <= test.mysequence
AND mynumber IS NOT NULL
)
);