This query will work fine. You could also do something like: UPDATE tips SET totalUsed = totalUsed - (SELECT MIN(totalUsed) - 1 FROM tips);
which would include the extra 1 (the new base) in the scalar subquery. The expression (SELECT MIN(totalUsed) FROM tips) is not correlated with the outer query (the update) and is a scalar value that is computed only once (when the first row of the outer update is processed), so the query effectively becomes equivalent to the following: begin; v = select min(totalUsed) from tips; update tips set totalUsed = totalused - v + 1; commit; where v would be passed in and out by your application, or, if you include the extra +1 operation in the scalar subquery then begin; v = select min(totalUsed) - 1 from tips; update tips set totalUsed = totalUsed - v; commit; The only difference being whether there is an additional "add" being performed for each row in the original ... If you do an "explain" on the query you see that there is a ONCE instruction (at addr 9) which means to skip the calculation of the scalar after it has been done once (by jumping to location 26 if it has already been calculated once). (The only difference being whether the offset of 1 is done inside the once set, or for each row). sqlite> explain update x set x = x - (select min(x) from x) + 1; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 37 0 00 Start at 37 1 Null 0 1 2 00 r[1..2]=NULL 2 OpenWrite 0 2 0 1 00 root=2 iDb=0; x 3 Noop 0 0 0 00 Begin WHERE-loop0: x 4 Rewind 0 35 0 00 5 Noop 0 0 0 00 Begin WHERE-core 6 Rowid 0 2 0 00 r[2]=rowid 7 IsNull 2 36 0 00 if r[2]==NULL goto 36 8 Column 0 0 5 00 r[5]=x.x 9 Once 0 26 0 00 10 Null 0 7 7 00 r[7..7]=NULL; Init subquery result 11 Integer 1 8 0 00 r[8]=1; LIMIT counter 12 Null 0 9 10 00 r[9..10]=NULL 13 OpenRead 1 2 0 1 00 root=2 iDb=0; x 14 Noop 0 0 0 00 Begin WHERE-loop0: x 15 Rewind 1 22 0 00 16 Noop 0 0 0 00 Begin WHERE-core 17 Column 1 0 11 00 r[11]=x.x 18 CollSeq 0 0 0 (BINARY) 00 19 AggStep 0 11 9 min(1) 01 accum=r[9] step(r[11]) 20 Noop 0 0 0 00 End WHERE-core 21 Next 1 16 0 01 22 Noop 0 0 0 00 End WHERE-loop0: x 23 AggFinal 9 1 0 min(1) 00 accum=r[9] N=1 24 Copy 9 7 0 00 r[7]=r[9] 25 DecrJumpZero 8 26 0 00 if (--r[8])==0 goto 26 26 Subtract 7 5 4 00 r[4]=r[5]-r[7] 27 Add 12 4 3 00 r[3]=r[12]+r[4] 28 Noop 0 0 0 00 BEGIN: GenCnstCks(0,1,2,2,0) 29 Noop 0 0 0 00 END: GenCnstCks(0) 30 Delete 0 68 2 x 02 31 MakeRecord 3 1 4 D 00 r[4]=mkrec(r[3]) 32 Insert 0 4 2 x 07 intkey=r[2] data=r[4] 33 Noop 0 0 0 00 End WHERE-core 34 Next 0 5 0 01 35 Noop 0 0 0 00 End WHERE-loop0: x 36 Halt 0 0 0 00 37 Transaction 0 1 1 0 01 usesStmtJournal=0 38 Integer 1 12 0 00 r[12]=1 39 Goto 0 1 0 00 sqlite> explain update x set x = x - (select min(x)-1 from x); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 36 0 00 Start at 36 1 Null 0 1 2 00 r[1..2]=NULL 2 OpenWrite 0 2 0 1 00 root=2 iDb=0; x 3 Noop 0 0 0 00 Begin WHERE-loop0: x 4 Rewind 0 34 0 00 5 Noop 0 0 0 00 Begin WHERE-core 6 Rowid 0 2 0 00 r[2]=rowid 7 IsNull 2 35 0 00 if r[2]==NULL goto 35 8 Column 0 0 4 00 r[4]=x.x 9 Once 0 26 0 00 10 Null 0 6 6 00 r[6..6]=NULL; Init subquery result 11 Integer 1 7 0 00 r[7]=1; LIMIT counter 12 Null 0 8 9 00 r[8..9]=NULL 13 OpenRead 1 2 0 1 00 root=2 iDb=0; x 14 Noop 0 0 0 00 Begin WHERE-loop0: x 15 Rewind 1 22 0 00 16 Noop 0 0 0 00 Begin WHERE-core 17 Column 1 0 10 00 r[10]=x.x 18 CollSeq 0 0 0 (BINARY) 00 19 AggStep 0 10 8 min(1) 01 accum=r[8] step(r[10]) 20 Noop 0 0 0 00 End WHERE-core 21 Next 1 16 0 01 22 Noop 0 0 0 00 End WHERE-loop0: x 23 AggFinal 8 1 0 min(1) 00 accum=r[8] N=1 24 Subtract 11 8 6 00 r[6]=r[8]-r[11] 25 DecrJumpZero 7 26 0 00 if (--r[7])==0 goto 26 26 Subtract 6 4 3 00 r[3]=r[4]-r[6] 27 Noop 0 0 0 00 BEGIN: GenCnstCks(0,1,2,2,0) 28 Noop 0 0 0 00 END: GenCnstCks(0) 29 Delete 0 68 2 x 02 30 MakeRecord 3 1 4 D 00 r[4]=mkrec(r[3]) 31 Insert 0 4 2 x 07 intkey=r[2] data=r[4] 32 Noop 0 0 0 00 End WHERE-core 33 Next 0 5 0 01 34 Noop 0 0 0 00 End WHERE-loop0: x 35 Halt 0 0 0 00 36 Transaction 0 1 1 0 01 usesStmtJournal=0 37 Integer 1 11 0 00 r[11]=1 38 Goto 0 1 0 00 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >Sent: Thursday, 12 July, 2018 01:30 >To: SQLite mailing list >Subject: [sqlite] Lowering totalUsed > >A few tables have a not completely apt named column totalUsed. > >It is used to see which records are more used as other records and >give the >less used records a bigger chance of being selected. When the numbers >become high I do something like: >UPDATE tips >SET totalUsed = totalUsed - (SELECT MIN(totalUsed) FROM tips) + 1 > >I am not quit happy with this. Would it be better to split it in two >queries and feed the result of the first to the second? > >-- >Cecil Westerhof >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
