[CALUG] Database question
brownclan at gmail.com
Mon Aug 21 09:13:36 CDT 2006
On 8/21/06, Jason C. Miller <jason.c.miller at gmail.com> wrote:
> Does anybody know of any queries or tricks to update an entire table in
> fewest operations possible? Before anyone freaks out and starts yelling
> UPDATE() at me, let me explain the scenario and also mention that I'm
> a DB/SQL guru. I've always known just enough to get by.
> Here's the scenario...
> Table A: (users)
> id | name | location | level
> 0 | nobody | nowhere | 3
> 1 | somebody | somewhere | 2
> 2 | anybody | anywhere | 1
> When a person makes changes to these values from my interface, they are
> actually doing it in some structures in memory that are initially an exact
> copy of the database table. Once they are happy with their changes, they
> will run a commit() which I will later implement to go through and make
> of the changes to the table from the changes that were made to the copy in
> memory. Normally, I would do this incrementally by writing a loop that
> would check for insert()s, delete()s, and update()s. However, if there's
> some SQL voodoo that I could use to do some kind of a sync() between the
> virtual table and the database table, I'd much prefer that. Does
> like this exist? I did some searching on the web but, as usual, don't
> the EXACT words to search for as not to get bombarded with 90,000,000,000
> irrelevant results.
> P.S. I'm using SQLite
Up until the "using SQLite," I was thinking "postgres and BEGIN
With transaction support, you do your changes and if something is borked
then postgres complains and the transaction fails. You then run
"ROLLBACK;" and the changes are removed. If everything goes well, you run
"COMMIT;" and the changes are made.
With SQLite, I think you are talking about writing transaction support. I'm
not a DBA guru either, but I think maybe it's time to move up to a heavier
weight db engine.
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the lug