SQLite Conditional Insertion

Question

I'm struggling to understand how or if I can make a conditional insertion using SQLite.

I want to insert rows of data, but I would prefer if, during insertion, a check can be done to decide if the row of data should be inserted.

Each row has a timestamp value which is set to NOW() (i know that's not a SQLite function, its just for simplicity here) on insertion.

Let's say a row contains the following fields:
timestamp, Name, Size, Nickname

I want it to check if there is already existing in the table a row that has the same Name, Size, Nickname values. If timestamp is the only thing that differs, we should NOT INSERT the row data.

If any of Name, Size, Nickname differs, we should INSERT the data.

If there a way to achieve this in SQLite?

PS: I also would like if this can work with mass VALUES insertion. So my existing INSERT SQL is something like:

INSERT INTO table (timestamp, name, size, nickname) VALUES (NOW(),"Fredrick","25","Fred"), (NOW(),"Margaret","21","Marg"), ... etc with lots of rows of data in a single insert

Answer 1

Simple solution
Try to send Sanatized data
Already in a correct format from JS or python

Answer 2

The data is sanitized. It needs to be compared against what already exists in the database at some point, and I am trying to see if it's possible to do that during the insertion operation.

If SQLite is simply not able to do such a conditional insertion of a large set of data, then I will need to consider some other method to do data comparison.

Answer 3

If you have a unique primary key in your table, you can use a `REPLACE INTO` statement for this purpose, it looks for entries with the same id and does an update if it finds one or an insert if it doesn't.

If that's not the right solution I would create a temporary table in your database, then you can do a plain `INSERT`, and another `UPDATE`.

INSERT INTO blah
SELECT blah FROM temp_table
WHERE blah

I guess you could also

INSERT INTO blah
SELECT blah FROM (
(NOW(), "Frederick", 25, "Fred"),
...
)
WHERE blah

but that's kinda weird.