Pejayuk wrote:
> 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:"
There is no need to open the memory database. SQLite will implicitly
open the memory database when you attach to it. In fact your
stats_memory database is a second independent memory database which is
unused by the rest of your processing.
> //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;"
There is no need to drop tables in memory databases. They simply
disappear when the memory is released.
> es_xsql query stats_static "DETACH stats_memory"
> es_xsql close stats_memory
Also, no need to close the memory database. It will be close
automatically when it is detached.
> 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.
Then this should be the primary key in both tables. Try this instead.
CREATE TABLE stats_memory.stats (
rank int(6),
steamid varchar(25) primary key,
playername varchar(80),
zkills int(6),
hkills int(6),
deaths int(6),
degrade int(6),
prounds int(6),
lastvisit int(20),
points NUMERIC(12)
);
> 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.
>
When doing and INSERT OR REPLACE, the record is replaced if the primary
key already exists in the table, otherwise it does an insert with the
new primary key.
>
> 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.
This will all happen automatically if you make steamid the primary key
because a primary key must be unique in each record of a table, and the
replace option happens when a new record would violate a uniqueness
constraint. See http://www.sqlite.org/lang_insert.html and
http://www.sqlite.org/lang_conflict.html for additional details.
>
>
> I hope this is a better explanation than before.
Much clearer. :-)
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
