[CALUG] Database question

[Hugh Brown]

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
> the
> fewest operations possible?  Before anyone freaks out and starts yelling
> UPDATE() at me, let me explain the scenario and also mention that I'm
> hardly
> 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
> all
> 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
> something
> like this exist?  I did some searching on the web but, as usual, don't
> know
> the EXACT words to search for as not to get bombarded with 90,000,000,000
> irrelevant results.
>
> Ideas?
>
>                                                                  -jason
>
> P.S. I'm using SQLite




Up until the "using SQLite," I was thinking "postgres and BEGIN
TRANSACTION;"

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.

Hugh
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://calug.com/pipermail/lug/attachments/20060821/a6da180f/attachment.html