sqlite - Insert only if last row differs - performance considerations - Stack Overflow

admin管理员组

文章数量:1317906

Having a pretty simple dataset in SQLite3. I wish INSERT to be executed only-if last row's data is different.

I want to avoid TRIGGER: I need to let my application decide whether the above filtering is required, or not (in case not, there is a simple insertion).

Here is the SQLFiddle demo.

Table definition:

CREATE TABLE Test (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  date datetime,
  text TEXT,
  text2 TEXT
);

Sometimes I need to insert data only-if last insertion is different (text field is different than our to-be-inserted text).

So I need to use some basic sub-query which gets me permission in case last row is not the same.

Can I do this subquery within one step (i.e. not more subqueries):

Something like this, but this is having syntax-error.

SELECT 1 FROM Test ORDER BY id DESC LIMIT 1 HAVING text="mynewtext"

Maybe with WINDOW functions? Or simple, single subquery:

INSERT INTO Test
SELECT null, '2025-01-22 10:10:03', 'Data5', 'DATA2'
WHERE (
SELECT (text!="Data5")+(text2!="DATA2") from Test ORDER BY id DESC LIMIT 1);

This above solution works. But I keep it here as reference, some may have idea to improve.

Having a pretty simple dataset in SQLite3. I wish INSERT to be executed only-if last row's data is different.

I want to avoid TRIGGER: I need to let my application decide whether the above filtering is required, or not (in case not, there is a simple insertion).

Here is the SQLFiddle demo.

Table definition:

CREATE TABLE Test (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  date datetime,
  text TEXT,
  text2 TEXT
);

Sometimes I need to insert data only-if last insertion is different (text field is different than our to-be-inserted text).

So I need to use some basic sub-query which gets me permission in case last row is not the same.

Can I do this subquery within one step (i.e. not more subqueries):

Something like this, but this is having syntax-error.

SELECT 1 FROM Test ORDER BY id DESC LIMIT 1 HAVING text="mynewtext"

Maybe with WINDOW functions? Or simple, single subquery:

INSERT INTO Test
SELECT null, '2025-01-22 10:10:03', 'Data5', 'DATA2'
WHERE (
SELECT (text!="Data5")+(text2!="DATA2") from Test ORDER BY id DESC LIMIT 1);

This above solution works. But I keep it here as reference, some may have idea to improve.

• sqlite

Share Improve this question Follow edited Jan 22 at 18:23 Daniel asked Jan 22 at 17:06 DanielDaniel 2,60244 gold badges3232 silver badges7575 bronze badges 2

• If text can be UNIQUE, you can use REPLACE sqlitetutorial/sqlite-replace-statement – yotheguitou Commented Jan 23 at 13:22
• Unfortunately neither text nor text2 can be unique. – Daniel Commented Jan 23 at 17:02

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Something like this:

INSERT INTO Test(date, text, text2)
SELECT n.*
FROM (
  VALUES('2025-01-22 10:10:03', 'Data5', 'DATA2')
) AS n
WHERE (n.column2, n.column3) NOT IN ( 
  SELECT text, text2
  FROM Test
  ORDER BY id DESC
  LIMIT 1
);

本文标签： sqliteInsert only if last row differsperformance considerationsStack Overflow