Sqlite - smart insert or replace query

Question

I have a question about sqlite.

Assuming I created a table:

CREATE TABLE GOODS(ID TEXT  PRIMARY KEY NOT NULL, TYPE TEXT, VERSION INTEGER)

Then after a while I want to use 'insert or replace' like this:

insert or replace into GOODS(ID, TYPE,VERSION) values ('chrome_id', 'chrome', 15);

But I want this query to 'replace' iff old.VERSION < new.VERSION.

How can I do that?

Do I need to use 2 queries (it should looks like this:) ?

1. get row
if(row.VERSION < myVersion){
2. insert or replace ... 
}

I heard about trigger but I do think this the solution. thx

NO. REPLACE INTO will INSERT the value if not found or UPDATE it if found. You need a single **command** (queries are only SELECT). — Phantômaxx, Feb 14 '17 at 08:42
As far as I understand replace is: DELETE + INSERT.When I wrote "how can I do it?" I actually meant to a single command so what do you mean when you say that I need a single command what do you mean exactly ? @Rotwang — wantToLearn, Feb 14 '17 at 08:50
You need a single command (REPLACE INTO). What it does behind the scenes to accomplish your command is not your business. You tell it to execute **one** command, and it executes **two** for you. — Phantômaxx, Feb 14 '17 at 08:53
So You saying that I need something like: replace into GOODS(ID, TYPE,VERSION) values ('chrome_id', 'chrome', 15) where VERSION < 15; but the syntax is not good @Rotwang — wantToLearn, Feb 14 '17 at 09:08

score 1 · Answer 1 · answered Feb 14 '17 at 09:13

Use the SELECT form of the INSERT statement to be able to control how many rows are inserted:

INSERT OR REPLACE INTO Goods(ID, Type, Version)
SELECT 'chrome_id', 'chrome', 15
WHERE NOT EXISTS (SELECT *
                  FROM Goods
                  WHERE ID = 'chrome_id'
                    AND Version >= 15);

Sqlite - smart insert or replace query

1 Answers1

Linked