On 7 Feb 2014, at 11:32am, Constantine Yannakopoulos <alfasud...@gmail.com> wrote:
> On Fri, Feb 7, 2014 at 1:27 PM, Simon Slavin <slav...@bigfraud.org> wrote: > >> Don't do it like that. >> >> Use 'INSERT OR IGNORE' to insert a record with a zero amount. >> Then update the record which, now, definitely does exist. > > Obviously you didn't read all of my message. :) You're right. Let's see if I can make it up to you. I do think you came up with the right strategy, and that doing INSERT OR IGNORE is going to give you the best result overall. Two scans on a field which is indexed won't take long. Probably less time in total than those MERGE commands or some complicated triggers. A few notes: An alternative way to do it would simply be not to store current balances. Whenever you want to know the balance as of a particular date, calculate it using total(). Not only does it mean smaller files and faster inputs, but you never have to sanity-check your totals and for a system which has more inputs than reports it is faster overall. This technique is increasingly used in systems used by banks and in double-entry bookkeeping. I assume you have a good reason for not keeping the CustomerAccum.amount field in the customer table. You did mention that your real scenario was more complicated than you were giving details of in your post, so that would explain it. Also, SQLite does not have VARCHAR fields (they're stored as text and the '50' will be ignored) or DATE fields. Dates must be stored as numbers or text. FLOAT will be interpreted as REAL, and will work the same way, so that's okay, but if I was doing something primarily for backing or accounting purposes I wouldn't use a REAL field, I'd use INTEGER and multiply by 100. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
