SQLite Insert OR update without losing record data

问题:

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

回答1:

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

回答2:

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

回答3:

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.