sql - How can I make this update query more efficient -

sql - How can I make this update query more efficient -

i'm using sqlite3. can see how create query more efficient? essentially, have table insert records given insertdate. table might contain duplicates (on 4 other fields) newly inserted records. want update field in duplicates

to find duplicate rows utilize query (identifiers changed clarity)

select table1.rowid table1 inner bring together ( select field1, field2, field3, field4, insertdate table1 insertdate = '2013-07-11' ) t on t.field1 = table1.field1 , t.field2 = table1.field2 , t.field3 = table1.field3 , t.field4 = table1.field4 table1.insertdate <> t.insertdate --(could utilize table1.insertdate <> '2013-07-11')

that select query takes 6s run indexes on fields 1,2 , 3 (interestingly found having index on field4 increased time 9s didn't have one)

i utilize query in update query, deciding records update having select next in clause in update statement ie

update table 1 set field 5 = 'a' table1.rowid in -- here comes original select query ( select table1.rowid table1 inner bring together ( select field1, field2, field3, field4, insertdate table1 insertdate = '2013-07-11' ) t on t.field1 = table1.field1 , t.field2 = table1.field2 , t.field3 = table1.field3 , t.field4 = table1.field4 table1.insertdate <> t.insertdate )

but take 25 seconds run!

i tried making temp table out of intial select, intending utilize in update query took around 25s create table doen't actual updating that's slowing things down, seens using 6s select query 'in'.

typically updating 9000 rows out of 300,000.

any suggestions improve query welcome.

if understand logic correctly, don't need rowid:

update table 1 set field 5 = 'a' insertdate <> '2013-07-11' , exists (select 1 table1 tt tt.insertdate = '2013-07-11' , t.field1 = tt.field1 , t.field2 = tt.field2 , t.field3 = tt.field3 , t.field4 = tt.field4 );

then, create index on table1(field1, field2, field3, field4, insertdate) , table1(insertdate).

sql sqlite3