mercredi 15 juillet 2015

SQLITE - Delete rows with self join?

I am trying to delete all rows in a simple table that have a duplicate value except for the duplicate with the highest id.

Table:

CREATE TABLE IF NOT EXISTS [Expression] (
                      [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                      [Value] VARCHAR(2048) NOT NULL
                      )

Attempted Queries:

DELETE Expression 
WHERE EXISTS (
    SELECT 1 
    FROM Expression Exp2 
    WHERE Expression.Value=Exp2.Value 
        AND Expression.Id < Exp2.Id)

fails with "SQL logic error or missing database near "Expression":syntax error"

DELETE Exp1 
FROM Expression Exp1 
  INNER JOIN Expression Exp2 
    ON Exp1.Value=Exp2.Value AND Exp1.Id < Exp2.Id

fails with "SQL logic error or missing database near "Exp1":syntax error"

What syntax do I need to use?

Aucun commentaire:

Enregistrer un commentaire