sqlite - SQL get rows with similar data for multiple rows -

sqlite - SQL get rows with similar data for multiple rows -

i have table id (pk), fixed (integer) , lot of other columns (name,content,comment,...)

when create version of info want check if new or updated rows have duplicate row fixed value of 1 or higher. (to edit rows fixed values, need more privileges)

id | fixed | name 1 | 1 | "john" 2 | 0 | "john" //new row 3 | 0 | "jane" // updated row

after creating version history should become:

id | fixed | name 1 | 2 | "john" 3 | 1 | "jane" 4 | 0 | "john" //current row 5 | 0 | "jane" //current row

this query allows me find id value of "fixed" row of new or updated rows (if know id's beforehand).

select t.id mytable t inner bring together ( select name, comments mytable id = 2) e on t.name = e.name , t.comments = e.comments t.fixed = 1

this query works uncertainty it's best solution.

i have next questions/concerns:

1) have run query every new or updated row. every time comparing 1 row against other rows in table. isn't efficient. certainly there improve way?

2) can search against id's (2 , 3) in 1 query , homecoming matching id's (or null if there isn't any)?

3) should compare relevant info columns (e.g. name). there way select or inner bring together on columns except columns (id, fixed)?

first, can rewrite query join:

select t.id mytable t inner bring together mytable e on t.name = e.name , t.comments = e.comments , e.id = 2 t.fixed = 1;

for performance, want index on mytable(fixed, name, comments, id).

if want homecoming null value utilize left outer join. list, utilize in:

select t.id mytable t left outer bring together mytable e on t.name = e.name , t.comments = e.comments , e.id in (2, 3) t.fixed = 1;

sql sqlite