Delete From SQLite Using Select Statement

Question

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?

CL. · Accepted Answer · 2014-01-23 08:11:58Z

1

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.)

answered Jan 23, 2014 at 8:11

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete From SQLite Using Select Statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related