SQLite Insert OR update without losing record data

Question

i have a table "test" with primary keys on "id & "lang"

╔════╦══════╦══════╦═══════╗
║ id ║ lang ║ test ║ test2 ║
╠════╬══════╬══════╬═══════╣
║  1 ║ zh   ║ lol3 ║ lol4  ║
║  1 ║ en   ║ lol  ║ qsdf  ║
╚════╩══════╩══════╩═══════╝

and i want to do insert or update but since you cannot do IF statement im left out with INSER OR REPLACE INTO

when i run my query :

INSERT OR REPLACE INTO test (id,lang,test) VALUES (1,'en','zaki')

i get this

╔════╦══════╦══════╦════════╗
║ id ║ lang ║ test ║ test2  ║
╠════╬══════╬══════╬════════╣
║  1 ║ zh   ║ lol3 ║ lol4   ║
║  1 ║ en   ║ zaki ║ <null> ║
╚════╩══════╩══════╩════════╝

It deletes the record and inserts a new one with available data ( like the documentation of sqlite states. ) But i want to keep the value of test2 ( but without knowing i need to keep the value of test2 ) i only know which value to replace not which to keep. is it possible to not lose the data i pass through (in 1 query).

possible duplicate of [SQLite - UPSERT \*not\* INSERT or REPLACE](http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace) — T.J. Crowder, Mar 10 '14 at 16:34
its certainly not a dupe since i don't know which columns i need to keep only the columns i need to change ( so i cannot use a select statement to place old values ) — Andy Jacobs, Mar 10 '14 at 16:47

laalto · Answer 1 · 2014-03-10T16:43:54.763

Most straightforward would be to use two queries conditionally, such as INSERT OR IGNORE followed by UPDATE conditionally, but if you insist on single query, you can try a subquery:

INSERT OR REPLACE INTO test (id, lang, test, test2)
  VALUES (1, 'en', 'zaki',
    (SELECT test2 FROM test WHERE id=1 AND lang='en'));

Example:

sqlite> create table test(id, lang, test, test2, primary key(id,lang));
sqlite> insert into test select 1,'zh','lol3','lol4';
sqlite> insert into test select 1,'en','lol','qsdf';
sqlite> insert or replace into test(id,lang,test,test2) values(1,'en','zaki', (select test2 from test where id=1 and lang='en'));
sqlite> select * from test;
1|zh|lol3|lol4
1|en|zaki|qsdf

score 2 · Answer 2 · answered Mar 10 '14 at 18:17

SQLite has no UPSERT, and it is designed to be embedded in some program. You are supposed to implement the IF in whatever language you are using there:

c.execute("SELECT 1 FROM test WHERE id = ? AND lang = ?", (1, "en"))
if c.fetchone():
    c.execute("UPDATE test SET test = ? WHERE id = ? AND lang = ?",
              ("zaki", 1, "en"))
else:
    c.execute("INSERT INTO test(test, id, lang) VALUES(?,?,?)",
              ("zaki", 1, "en"))

This could be optimized a little bit with INSERT OR IGNORE:

c.execute("INSERT OR IGNORE INTO test(id, lang, test) VALUES(?,?,?)",
          ("zaki", 1, "en"))
if not c.lastrowid:
    c.execute("UPDATE test SET test = ? WHERE id = ? AND lang = ?",
              ("zaki", 1, "en"))

score 0 · Answer 3 · answered Mar 10 '14 at 16:33

0

It would seem that your primary key is on (id, lang) and your query found an existing record with id = 1, lang = 'en' and did a REPLACE on it using the data you specified.

answered Mar 10 '14 at 16:33

FtDRbwLXw6

27,774
13
70
107

yes i know my primary keys are on id & lang i forgot to mention it. But the real question is how can i transform my query to an update instead of replace. (so i can update 1 certain value and not required to pass all values ( which i don't have at that point) – Andy Jacobs Mar 10 '14 at 16:43
@AndyJacobs You're giving the database a conflicting primary key values and telling it to replace the data with what you give it when there's a conflict. If you're trying to insert a *new* record, then you need to give it primary key values that don't already exist... – FtDRbwLXw6 Mar 10 '14 at 16:45
i provided more detail in the topic, maybe my description wasn't thorough enough – Andy Jacobs Mar 10 '14 at 16:50

SQLite Insert OR update without losing record data

3 Answers3