Inserting a new row without overriding columns if the new value of the column is null

Question

Say I have a table MyTableName with three columns: COLUMN_NAME_A, COLUMN_NAME_B, COLUMN_NAME_C (only three for simplification, in practice I may have dozens of columns). With my current implementation, when I insert a new row, all columns will be overridden. How can I override only columns for which the new value is not null. One way, is to check the new value, and if it is NULL, read the value from the table and rewrite it. Is there a more simple and/generic way to do so?

- (void)insertEntries:(<id<PTDBGeneralInfoTableEntry>>)entry inDatabase:(FMDatabase *)db {

  [db beginTransaction];

  [self createTableInDatabase:db];

  NSString *insertSQL = [NSString stringWithFormat:@"INSERT OR REPLACE INTO MyTableName"
                         "(%@, %@, %@)"
                         " VALUES(?, ?, ?)",
                         COLUMN_NAME_A,
                         COLUMN_NAME_B,
                         COLUMN_NAME_C];

  NSArray* values = @[entry.valueA ? entry.valueA : [NSNull null],
                      entry.valueB ? entry.valueB : [NSNull null],
                      entry.ValueC ? entry.ValueC : [NSNull null]];

  BOOL res = [db executeUpdate:insertSQL withArgumentsInArray:values];


  [db commit];
}

- (void)createTableInDatabase:(FMDatabase *)db {

  NSString* createTableSQL =
  [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ ("
   "%@ TEXT NOT NULL PRIMARY KEY,"
   "%@ INTEGER,"            
   "%@ REAL"             
   ")",
   COLUMN_NAME_A,
   COLUMN_NAME_B
   COLUMN_NAME_C
   ];

   [db executeUpdate:createTableSQL];

}

szmate1618 · Answer 1 · 2018-10-31T22:45:35.533

I think you have 2 options. The first one is ugly and probably slower, but as far as I know is standard SQL and is widely supported. The second one is not standard, but is supported by SQLite since version 3.24.0.

Embedded select

Based on this answer, you can do something like

INSERT OR REPLACE INTO MyTableName
VALUES (
    'example_id',
     COALESCE(100, (SELECT COLUMN_NAME_B from MyTableName WHERE COLUMN_NAME_A = 'example_id')),
     COALESCE(1.0, (SELECT COLUMN_NAME_C from MyTableName WHERE COLUMN_NAME_A = 'example_id'))
);

This is fairly straightforward, but also a bit ugly, and potentially slow. The more columns you have the more embedded selects you need to do (actually you only need them for columns with potential null values, but still), and the more rows you have, the more time a select takes.

Upsert

But if you are on SQLite version 3.24.0 or newer, you can use the so called upsert syntax

INSERT INTO MyTableName
VALUES ('example_id', 100, 1.0)
ON CONFLICT(COLUMN_NAME_A) DO UPDATE SET
    COLUMN_NAME_B = COALESCE(EXCLUDED.COLUMN_NAME_B, COLUMN_NAME_B),
    COLUMN_NAME_C = COALESCE(EXCLUDED.COLUMN_NAME_C, COLUMN_NAME_C);

where EXCLUDED.COLUMN_NAME_ refers to the new value to be inserted (which is 'excluded', until we resolve of the conflict).

I'm getting: 'near "ON": syntax error'. My Sqlite version is 3.24.0 — Sanich, Nov 01 '18 at 12:49
That's strange, it seems to work for me. My exact version is: `sqlite>.version SQLite 3.24.0 2018-06-04 19:24:41 c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca zlib version 1.2.11 gcc-5.2.0` — szmate1618, Nov 01 '18 at 14:05
I'm on Windows 10 by the way, using the precompiled binaries downloaded from the official site https://www.sqlite.org/download.html. The links have been updated to point to 3.25.2, but the original URL are still alive. — szmate1618, Nov 01 '18 at 14:21

Inserting a new row without overriding columns if the new value of the column is null

1 Answers1

Embedded select

Upsert