SQL: using the newly inserted index as a value for insertion

[Peter Haworth]

Assuming there really is a column named key, I don't know why you're
getting the error, maybe because key is a reserved word.

However, as mentioned in my last email, I think David's solution is better
than my idea of using a trigger.  Your UPDATE statement would be:

UPDATE remoteTable SET cmd='INSERT INTO localTable (key,stuff)
   VALUES(' || (SELECT last_insert_rowid()) || ',  ''this is some stuff''
); ' WHERE
scratch='scratchKey';

I think it's OK to include a SELECT statement as a value in an UPDATE
command but not 100% sure.

Just to clarify on the trigger statement, the trigger would be defined to
happen on an INSERT to remotetable, not an UPDATE and if you decided to go
that route, then the SLECT statement above would be replaced by "new.rowid"

Pete
lcSQL Software <http://www.lcsql.com>


On Sun, Nov 24, 2013 at 9:33 AM, Dr. Hawkins <dochawk at gmail.com> wrote:

> On Sat, Nov 23, 2013 at 10:26 PM, Peter Haworth <pete at lcsql.com> wrote:
>
> > You want the word key to be replaced by the value of the primary key from
> > the row from the INSERT statement?
> >
>
> Yes; the primary key assigned on the previous command.
>
>
> > If so,  that's how the trigger can help.  You can refer to any column
> from
> > the INSERT using the syntax "new.colname" where colname is the name of
> any
> > column in master table.
> >
>
> What I'm missing (and is probably fundamental) is *why* I can't use the
> value of the column in my UPDATE, and why postgres answers that the column
> doesn't exist.
>
> If I run
>
> BEGIN TRANSACTION;
>   UPDATE theTable SET price=100 WHERE key=7;
>   UPDATE theTable SET profit=price/3 WHERE key=7;
> END TRANSACTION;
>
> I should get profit set to 33 in that record (subject to rollback).
>
> I'm not seeing the difference between setting that value in an insert and
> using it the next line in an update on the one hadnd, and changing in an
> update then using that changed value on the next line.
>
> Sure, I'm using it as part of a string operation, but postgres is supposed
> to support those.
>
> And won't a trigger be expensive, taking time every time I UPDATE over a
> relatively uncommon event?
>
>
> > Still not 100% sure I understand but maybe that helps.
> >
>
> I think so, but I'm not there yet :)
>
> And isn't
>
>
>
>
> --
> 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
>