Thanks Dennis, I get the gist of it. I am getting duplicate records though using insert or replace. I was using update before.
>From the TOP. When my script loads it does the following fine. //Open static database. es_xsql open stats_static |zmstats //Open memory database. es_xsql open stats_memory ":memory:" //Attach memory database to static. es_xsql query stats_static "ATTACH ':memory:' as stats_memory;" //Create table in stats_static attached memory database. es_xsql query stats_static "CREATE TABLE stats_memory.stats (rank int(6), steamid varchar(25), playername varchar(80), zkills int(6), hkills int(6), deaths int(6), degrade int(6), prounds int(6), lastvisit int(20), points NUMERIC(12));" When my script unloads it does the following fine. //unload all es_xsql query stats_static "DROP TABLE stats_memory.stats;" es_xsql query stats_static "DETACH stats_memory" es_xsql close stats_memory es_xsql close stats_static As the players play on the server the fields in stats_memory.stats are updated with kills and deaths etc. After the map finishes my script needs to update the stats_static stats table with the records in stats_memory.stats, but instead of updating the records it creates new records. The unique thing about each row is the steamid of the player both in stats_static stats and stats_memory.stats. This is specific to the players account. I need these records to update or replace the existing record in stats_static stats table with the corresponding record in stats_memory.stats or if the records doesn't exist, it needs to insert a new record. This is the query that I believe is causing me the problem. //Update stats_static stats table with records from stats_memory.stats table. es_xsql query stats_static "INSERT OR REPLACE into main.stats select * from stats_memory.stats;" I believe the problem is with the sql statement part as follows. INSERT OR REPLACE into main.stats select * from stats_memory.stats; This should somehow using the steamid field in both tables as a way to choose if it should insert or replace. If the steamid from stats_memory.stats isn't in stats_static stats table then it should insert. If the steamid from stats_memory.stats is in stats_static stats table then it should replace or update it. I hope this is a better explanation than before. Many thanks for all your help so far. :-D --------------------------------------------------------------------------------------------------- Dennis Cote wrote: > > Pejayuk wrote: >> I still can't get this to work. >> Can someone help please. >> >> :working: >> >> >> Pejayuk wrote: >>> That is fantastic Igor. >>> Thankyou. >>> All I need to do now is work out how to get an update query to use a >>> select query to update the records from stats_memory to stats_static >>> after >>> doing the link. >>> I think google may be able to help me with that. >>> Thankyou for your time. >>> =) >>> >>> EDIT: >>> I have just had a look around google. I think the following is what I >>> need >>> to do but would like to know if someone can confirm I have this correct. >>> >>> I have the static_stats database and :memory: database open. >>> I then attach as follows. >>> >>> ATTACH ':memory:' as db1; >>> ATTACH 'stats_static' as db2; >>> >>> I then update the records in stats_static stats as follows from the >>> :memory: stats table. >>> INSERT OR REPLACE INTO db2.stats (rank, steamid, playername, zkills, >>> hkills, deaths, degrade, prounds, lastvisit, points) SELECT rank, >>> steamid, >>> playername, zkills, hkills, deaths, degrade, prounds, lastvisit, points >>> from db1.stats >>> >>> I then detach the databases as follows. >>> DETACH 'stats_static' >>> DETACH ':memory:' >>> >>> a) Would the insert or replace query as shown above, update the >>> stats_static database with the fields from :memory: ?. >>> b) After the detach command, would both the databases still be open as >>> they were before the attach command?. >>> >>> Many thanks in advance. >>> >>> EDIT2: >>> Another thought. >>> If I executed the following querys on the stats_static database. >>> >>> ATTACH ':memory:' as db1; >>> >>> INSERT OR REPLACE INTO stats SELECT rank, steamid, playername, zkills, >>> hkills, deaths, degrade, prounds, lastvisit, points from db1.stats >>> >>> DETACH ':memory:' >>> >>> Does that give me what I want. >>> Think this is more what I should be doing. >>> Can someone please confirm. >>> >>> Sorry for all the edits. >>> > > You would be better served if you took a little more time to format your > questions so they are clear to the reader. Rambling edits and pasting > content as quotations doesn't help. > > You need to open your permanent database as you normally would and then > execute an SQL attach command to attach the memory database. I'm don't > know the syntax for your eventscripts language, but if you can execute > queries this would be much the same. > > es_xsql open stats_static |zmstats > stats_static.execute "ATTACH ':memory:' as stats_temp" > stats_static.execute "create table stats_temp.stats (...)" > > Now copy data from the permanent database to the temp or vice versa. > Note, "main" is the implicit name of the database that was opened as > stats_static (i.e. by a call to sqlite3_open() by your eventscripts). > > stats_static.execute "INSERT into stats_temp.stats select * from > main.stats" > > Once the temp data is loaded it can be manipulated using normal SQL > commands and the table name "stats_temp.stats". > > When it is time to save the temp data simply copy it back. > > stats_static.execute "INSERT OR REPLACE into main.stats select * from > stats_temp.stats" > > Now you can detach the memory database and close the permanent database. > > stats_static.execute "DETACH stats_temp" > es_xsql close stats_static > > I'm doing a lot of reading between the lines, but I hope you get the > gist of it. > > HTH > Dennis Cote > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Update-static-database-with-records-from-memory-database.-tp18187288p18267278.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
