SQlite Insert或replace条件是每个列值都大于其当前值
SQlite Insert或replace条件是每个列值都大于其当前值
提问于 2019-07-11 04:26:03
我所处的情况是，我可以让相同的记录出现多次。
问题是一些字段可能会丢失，这是由该字段中的值-1表示的。该字段的值将为正确的值或-1。问题是我事先不知道哪些字段将丢失，并且每条记录的丢失字段集可能是不同的。我的目标是在数据库中有一个最终条目，它汇集了所有正确的值。
考虑到唯一可能的值是-1，并且正确的值是正数，这应该可以确保我最终得到尽可能多的正确行(当然，也可能会从所有记录中省略一些列)。
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute('''CREATE table IF NOT EXISTS SearchTable ([Owner] INTEGER
PRIMARY KEY, [Val1] INT, [Val2] INT)''')
c.execute("""INSERT OR REPLACE INTO SearchTable (Val1, Val2) values(-1,
5)""")
c.execute("""INSERT OR REPLACE INTO SearchTable (Owner, Val1, Val2)
values(1, 7, -1)""")下面的问题似乎要求类似的东西，但在这种情况下，您有一个完整的数据集进行比较，您不知道在我的案例中哪些(如果有) recs是完整的。所以我不能将这个解决方案推广到我的问题上。
How to conditionally INSERT OR REPLACE a row in SQLite?
这个答案我真的一点也不明白，但也许它会帮助你解决我的问题。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-07-11 08:37:40
我相信下面就是你想要做的事情。
如果您有3.24.0或更高版本的SQlite，则可以使用UPSERT (update或insert)
例如：
DROP TABLE IF EXISTS SearchTable;
CREATE TABLE IF NOT EXISTS SearchTable ([Owner] INTEGER PRIMARY KEY, [Val1] INT, [Val2] INT);
INSERT INTO SearchTable (Owner,Val1,Val2) VALUES(null,-1,5)
ON CONFLICT(Owner) DO UPDATE
SET
Val1 = CASE WHEN Val1 < 0 AND -1 /*<<<<<<<<<< value for val1 */ > 0 THEN -1 /*<<<<<<<<<< value for val1 */ ELSE Val1 END,
Val2 = CASE WHEN Val2 < 0 AND 5 /*<<<<<<<<<< value for val2 */ > 0 THEN 5 /*<<<<<<<<<< value for val2 */ ELSE Val2 END
;
INSERT INTO SearchTable (Owner,Val1,Val2) VALUES(1,7,-1)
ON CONFLICT(Owner) DO UPDATE
SET Val1 = CASE WHEN Val1 < 0 AND 7 /*<<<<<<<<< value for val1 */ > 0 THEN 7 /*<<<<<<<<<< value for val1 */ ELSE Val1 END,
Val2 = CASE WHEN Val2 < 0 AND 5 /*<<<<<<<<<<< value for val2 */ > 0 THEN 5 /*<<<<<<<<<< value for val2 */ ELSE Val2 END
;结果：
sqlite> INSERT INTO SearchTable (Owner,Val1,Val2) VALUES(null,-1,5) ON CONFLICT(Owner) DO UPDATE SET Val1 = CASE WHEN Val1 < 0 AND -1 > 0 THEN -1 ELSE Val1 END , Val2 = CASE WHEN Val2 < 0 AND 5 > 0 THEN 5 ELSE Val2 END;
sqlite> select * from SearchTable;
1|-1|5
sqlite> INSERT INTO SearchTable (Owner,Val1,Val2) VALUES(1,7,-1) ON CONFLICT(Owner) DO UPDATE SET Val1 = CASE WHEN Val1 < 0 AND 7 > 0 THEN 7 ELSE Val1 END, Val2 = CASE WHEN Val2 < 0 AND 5 > 0 THEN 5 ELSE Val2 END;
sqlite> select * from SearchTable;
1|7|5- 注释下一个示例包含更多解释性注释，其中许多都适用。
如果SQlite版本低于3.24.0，请考虑：
DROP TABLE IF EXISTS SearchTable;
CREATE TABLE IF NOT EXISTS SearchTable ([Owner] INTEGER PRIMARY KEY, [Val1] INT, [Val2] INT);
-- First Insert (null -1,5)
INSERT OR REPLACE INTO SearchTable ([Owner],Val1, Val2) VALUES(null /* owner null if first insert and auto generated value required, else known owner value */,
-- Handle VAL1 column
CASE
-- if no owner matching supplied value
WHEN (SELECT Val1 FROM SearchTable WHERE [owner] = null /*<<<<<<<<<< owner value */) IS NULL THEN -1 /* 1st value */
-- if current value is -1 then use supplied value
WHEN (SELECT Val1 FROM SearchTable WHERE [owner] = null /*<<<<<<<<< owner value */) = -1 THEN -1 /* 1st value */
-- if current value is o or greater then use current value
WHEN (SELECT Val1 FROM SearchTable WHERE [owner] = null /*<<<<<<<<< owner value */) > -1 THEN (SELECT Val1 FROM SearchTable WHERE [owner] = null /*<<<<<<<<<< owner value */)
END,
-- Handle Val2 Column
CASE
-- if no owner matching supplied value
WHEN (SELECT Val2 FROM SearchTable WHERE [owner] = null /*<<<<<<<<<< owner value */) IS NULL THEN 5 /* 2nd value */
-- if current value is -1 then use supplied value
WHEN (SELECT Val2 FROM SearchTable WHERE [owner] = null /*<<<<<<<<< owner value */) = -1 THEN 5 /* 2nd value */
-- if current value is 0 or greater then use current value
WHEN (SELECT Val2 FROM SearchTable WHERE [owner] = null /*<<<<<<<<< owner value */) > -1 THEN (SELECT Val2 FROM SearchTable WHERE [owner] = null /*<<<<<<<<<< owner value */)
END
);
-- Show result 1
SELECT * FROM SearchTable;
-- Second Insert (1,7,-1) !!!!Assumes that 5 should change to -1
INSERT OR REPLACE INTO SearchTable ([Owner],Val1, Val2) VALUES(1 /* owner null if first insert and auto generated value required, else known owner value */,
-- Handle VAL1 column
CASE
-- if no owner matching supplied value
WHEN (SELECT Val1 FROM SearchTable WHERE [owner] = 1 /*<<<<<<<<<< owner value */) IS NULL THEN 7 /* 1st value */
-- if current value is -1 then use supplied value
WHEN (SELECT Val1 FROM SearchTable WHERE [owner] = 1 /*<<<<<<<<< owner value */) = -1 THEN 7 /* 1st value */
-- if current value is 0 or greater then use current value
WHEN (SELECT Val1 FROM SearchTable WHERE [owner] = 1 /*<<<<<<<<< owner value */) > -1 THEN (SELECT Val1 FROM SearchTable WHERE [owner] = null /*<<<<<<<<<< owner value */)
END,
-- Handle Val2 Column
CASE
-- if no owner matching supplied value
WHEN (SELECT Val2 FROM SearchTable WHERE [owner] = 1 /*<<<<<<<<<< owner value */) IS NULL THEN -1 /* 2nd value */
-- if current value is -1 then use supplied value
WHEN (SELECT Val2 FROM SearchTable WHERE [owner] = 1 /*<<<<<<<<< owner value */) = -1 THEN -1 /* 2nd value */
-- if current value is 0 or greater then use current value
WHEN (SELECT Val2 FROM SearchTable WHERE [owner] = 1 /*<<<<<<<<< owner value */) > -1 THEN (SELECT Val2 FROM SearchTable WHERE [owner] = 1 /*<<<<<<<<<< owner value */)
END
);
-- Show result 2
SELECT * FROM SearchTable;这将首先使用提供的3个值(owner、val1和val1) null、-1和5添加行( owner的值为null) :-
第二次使用值(owner、val1和val1) 1、7和-1修改行
根据我的解释，
- Val2没有更新:-
- What I want to be able to do is to insert and replace those columns whose value has increased. Given that the only possible values are -1 and the correct value which is positive this should ensure that I end up with as many of the correct rows as possible (it may be of course that some columns are omitted from all the records.)
详情如下：
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56978148
复制相关文章
相似问题