SQlite在每个列值大于其当前值时插入或替换条件

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (25)

我处于一种情况,我可以多次出现相同的记录。

问题是某些字段可能丢失,在该字段中由值-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)""")

上面的代码是我的尝试(我仍然是sql的新手)显然这是错误的,它更新两列而不管它们的当前值,但它应该只更新Val1因为Val1已经增加,而Val2应该保持不变,因为它减少了。

下面的问题似乎是要求类似的东西,但在这种情况下,你有一个完整的数据集可供比较,你不知道在我的情况下哪些(如果有的话)recs是完整的。所以我无法将此解决方案概括为我的问题。

如何在SQLite中有条件地插入或替换行?

这个我根本无法理解答案,但也许它会帮助你解决我的问题。

SQLite条件插入或替换

提问于
用户回答回答于

我相信以下是您想要做的事情。

如果你有SQlite版本3.24.0或更高版本,那么你可以使用UPSERT(更新或插入)

例如: -

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版本低于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;

这最初添加行(owner的值为null),使用3个提供的值([owner],val1和val1)null,-1和5,如下所示: -

第二使用修正用的值([所有者],val1和VAL1)的行17-1

  • 根据我的解释,Val2没有更新: -
    • 我希望能够做的是插入和替换值增加的列。鉴于唯一可能的值为-1且正确的值为正,这应该确保我最终得到尽可能多的正确行(当然可能在所有记录中省略了一些列。)

按照 :-

扫码关注云+社区

领取腾讯云代金券