An SQL query to return an UPDATE for the results?

[Peter Haworth]

Don't know how many rows are involved but it would be much faster to select
all the rows from the memory db table with one SELECT, then build the
UPDATE statements using an LC script.  An wrap the whole thing in BEGIN/END.

As for the NULL issue, you could do two selects, one for rows where the
column IS NULL and one for rows where the column is NOT NULL, and format
the UPDATE statements accordingly.

If you're sending the UPDATE statements over a network, you should use the
LC feature of supplying the values in a list of variables/an array to avoid
SQL injection issues.

Also, is it possible that there won't be a matching row in the disk/netwrk
database?  If so, do the UPDATE and check if the return value is not zero;
if it is zero, it means no rows were updated and you need to do an INSERT
instead.

Not even gonna get into the issue os what happens if two users try to
update from memory at the same time, or perhaps that's not possible in your
application.

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


On Wed, Aug 7, 2013 at 7:49 AM, Dr. Hawkins <dochawk at gmail.com> wrote:

> With an SQLite database in memory, and a "real" database either on
> machine or remote, I need to sync the two.
>
> I'm trying to find a clever & fast way to create a SELECT query that
> produces the corresponding UPDATE query.
>
> I can do something like "SELECT " & 'UPDATE sometable SET col1=''" &
> col1 & "'' WHERE key=''" & keyval & "'';" WHERE key =' & keyval & "';"
>
> to return
>   UPDATE sometable SET col1='thevalue' WHERE key='keyval';
>
> but this doesn't accommodate NULL values, which would come back as
> empty but need to become NULL rather than ''
>
> I suppose I could do a
>    replace "''" with "NULL" in myQuery      --for null strings
>    replace ",," with ",NULL,"  in myQuery   --for null numbers
>
> (and,f or that matter, null strings don't matter much.
>
> Am I on to something, or am I missing something big here?
>
>
> --
> 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
>