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 <doch...@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@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
