SQlite Insert or replace conditional on each column value being bigger than it’s current value

I’m in a situation where I can have the same record appear several times.

The trouble is that some of the fields may be missing, this is denoted by a value of -1 in that field. The value of the field will either by the correct value or -1. The correct value will always be positive. The problem is I don’t know in advance which fields will be missing and the set of missing fields can be different for each record. My goal is to have a final entry in the database, which pools all the correct values.

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.)

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)""")

The code above is my attempt (I’m still new to sql) obviously this is wrong, it updates both columns regardless of their current values,but it should only update Val1 because Val1 has increased, while Val2 should remain unchanged because it decreased.

The question below seems to be asking for something similar but in that case, you have a complete data set to compare to, you don’t know which (if any) of the recs are complete in my case. So I’ve not been able to generalise this solution to my problem.

How to conditionally INSERT OR REPLACE a row in SQLite?

This one I really could not understand the answer at all but perhaps it will help you when trying to solve my problem.

SQLite conditional insert or replace

Answer

I believe the following is along the lines of what you want to do.

If you have version 3.24.0 or greater of SQlite then you can use an UPSERT (update or insert)

e.g. :-

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
    ;

Result :-

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

• Note the next example includes more explanatory notes, many of which apply.

If the SQlite version is below 3.24.0 then consider :-

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;

This initially adds the row (value for owner is null), using 3 supplied values ([owner], val1 and val1) null,-1 and 5 as per :-

The second use amends the row using values ([owner], val1 and val1) 1, 7 and -1

• Val2 is not updated according to my interpretation of :-

  • 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.)

As per :-