CODEWITHSUNDEEP
sqlsqlite
rwolst
rwolst

Reputation: 13672

Delete From SQLite Using Select Statement

I have the following SELECT statement,

SELECT * From MyTable as t
WHERE
t.Key1 =
(SELECT Max(r.Key1) From MyTable as r
WHERE (Key2 = t.Key2 AND Key3 = t.Key3 AND Key4 = t.Key4))
AND
(Key2 = t.Key2 AND Key3 = t.Key3 AND Key4 = t.Key4)

which selects all the records I wish to delete from MyTable. However when I try to actually delete the records i.e. by changing SELECT * to DELETE, I get an error and I believe this is because you cannot use aliases with a DELETE statement.

How then would I go about deleting these selected records from MyTable?

Upvotes: 0

Views: 140

Answers (1)

CL.
CL.

Reputation: 180020

It is indeed not possible to use an alias for the table name in a DELETE statement.

However, that table still has a name. As long as all other instances of this table have an alias, you can unambiguously refer to any of them:

DELETE From MyTable
WHERE
MyTable.Key1 =
(SELECT Max(r.Key1) From MyTable as r
WHERE (Key2 = MyTable.Key2 AND Key3 = MyTable.Key3 AND Key4 = MyTable.Key4))
AND
(Key2 = MyTable.Key2 AND Key3 = MyTable.Key3 AND Key4 = MyTable.Key4)

(The last line of this query does not make sense, because it is not part of the subquery and Key2 and MyTable.Key2 refer to the same table.)

Upvotes: 1

Related Questions