[OT] SQL question
pete at lcsql.com
Mon Nov 11 23:30:50 CET 2013
It seems Malte is having two problems - how to conditionally update a
column value and how to ensure everything stays locked during the update.
I think this should work:
INSERT INTO test2 (id,UUID,running_number) VALUES (3,:1,CASE WHEN (SELECT
count(UUID) FROM table2 WHERE UUID=:1)=0 THEN (SELECT first_number FROM
table1 WHERE UUID=:1)+1 ELSE (SELECT MAX(running_number FROM table2)+1 END;
lcSQL Software <http://www.lcsql.com>
On Mon, Nov 11, 2013 at 12:54 PM, Mike Kerner <MikeKerner at roadrunner.com>wrote:
> This is one of many reasons why triggers exist. There will be a single
> trigger process. Inside of the trigger you can assign the incrementing
> values, thus you will be guaranteed to get it to act the way you want, no
> matter what happens. The trigger locks the table while it is operating.
> In larger systems this has the potential to be a problem, or introduce
> deadlock, so you have to be careful, but there are just those cases where
> you absolutely, positively have to have something happen a certain way.
> You can also use a semaphore, but I would suggest you just use a trigger.
> On Mon, Nov 11, 2013 at 11:14 AM, Dr. Hawkins <dochawk at gmail.com> wrote:
> > On Mon, Nov 11, 2013 at 6:26 AM, Malte Brill <revolution at derbrill.de>
> > wrote:
> > > The Problem is, that postGreSQL appears to be locking tables only
> > > the transaction and automagically releases the lock, which is not ideal
> > in
> > > my situation.
> > You can use a begin/end lock to get everything done:
> > BEGIN TRANSACTION;
> > INSERT something;
> > SELECT something else
> > END TRANSACTION;
> > and either the whole block happens, or the db is untouched.
> > You could also make the column a serial or auto-incrementing key.
> > --
> > Dr. Richard E. Hawkins, Esq.
> > (702) 508-8462
> > _______________________________________________
> > use-livecode mailing list
> > use-livecode at lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your
> > subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
> and did a little diving.
> And God said, "This is good."
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
More information about the use-livecode