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

Question

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

You state that you want to insert or replace columns whose value has increased, but 1) how do you know what column has increased, and 2) how do you identify which rows should be updated with the increased values? Can even the OWNER (primary key) be -1? **You really need to show sample data, otherwise this really does not make any sense.** — C Perkins, Jul 10 '19 at 23:30
BTW, the primary condition on `INSERT OR REPLACE` is this: If the primary key exists, then replace the row with the new data, else insert a new row with a new primary key. The key idea in the other answers is that you use an INSERT OR REPLACE with a SELECT statement (instead of a VALUES list). The extra conditions are placed in the WHERE condition of the SELECT statement which can controls whether or not the SELECT statement will return any rows. If the SELECT statement condition is false overall, then it will return no rows and so no insert/replace. — C Perkins, Jul 10 '19 at 23:55
Is this really a good application for a database? It seems to me that this may be handled much more easily and efficiently using in-memory language constructs (dictionary?) rather than a database table. Do these rows need to persist between application sessions? Will there potentially be many rows that exceed memory limits? Will the goal of finding the "final entry" be obtained in a single session? The data could potentially be saved and re-loaded into another construct that is better suited to your dynamic requirements. — C Perkins, Jul 11 '19 at 00:36
Is the value `-1` necessary? Or could you use `null`? If you used null, you could probably take advantage of the coalesce function and other null handling logic for the missing values. — C Perkins, Jul 11 '19 at 00:39
1) How do you know what column has increased, and 2) How do you identify which rows should be updated with the increased values? This is the crux of the problem I need to be able to compare the existing values for each column with the proposed value and take whichever value is highest. Can even the OWNER (primary key) be -1? No — Bazman, Jul 11 '19 at 11:56
Will there potentially be many rows that exceed memory limits? Yes — Bazman, Jul 11 '19 at 11:57
Is the value -1 necessary? It isn't necessary and I could use null can you expand on how the coalesce function and other null handling logic could help here? — Bazman, Jul 11 '19 at 11:58

MikeT · Accepted Answer · 2019-07-11T01:42:25.663

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 :-

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

1 Answers1