sqlite delete duplicate values

[gcue]

hello.

i am trying to isolate distinct values from a table into a temporary table

delete the original table

then rename the temporary table to the original table

here's what i have so far

_SQLite_Exec(-1, "SELECT DISTINCT Initials,Script,ScriptDirectory,Asset INTO TMPaScriptUpdater FROM aScriptUpdater")
    _SQLite_Exec(-1, "TRUNCATE TABLE aScriptUpdater")
    _SQLite_Exec(-1, "DROP TABLE aScriptUpdater")
    _SQLite_Exec(-1, "ALTER TABLE TMPaScriptUpdater RENAME TO aScriptUpdater")

im getting an error (from the first line):

near "INTO": syntax error

thanks in advance for your help!

[jchd]

This SQL looks pretty much like non-standard extensions. Neither SELECT ... INTO ... nor TRUNCATE are SQLite-ish.

Besides, this way of getting rid of duplicates doesn't recreate the exact same original table. For that you'd have to deal with index(es), triggers, constraints, foreign keys a.s.o.

A more straitforward way is to remove duplicates directly. Doing so without overlooking anything is best done after carfully examining your schema. Can you post it (the CREATE statements used for that table, its indices, constraints, triggers, FKs ...)?

[gcue]

actually i got it to work with this:

_SQLite_Exec(-1, "CREATE TABLE TMPaScriptUpdater AS SELECT DISTINCT Initials,Script,ScriptDirectory,Asset FROM aScriptUpdater")
_SQLite_Exec(-1, "DROP TABLE aScriptUpdater")
_SQLite_Exec(-1, "ALTER TABLE TMPaScriptUpdater RENAME TO aScriptUpdater")

thanks for your help =)

Edited June 7, 2012 by gcue