SQLite: Combine SELECT and DELETE in one statemet

Question

Hi, I need to run two statements like so:

Select amount from db where ID=5
DELETE from db where ID=5

Currently I prepair a run two different statements. I wonder if there is a way to combine it in on statement.

Basically all I need to do is to get and amount column from the row before is is deleted.

thanks

Answer 1

SQLite does not support this extension to standard SQL -- you do have to use both statements, SELECT first, DELETE next. You can wrap them in a transaction, of course, (BEGIN and COMMIT statements before and after will guarantee that), to guarantee atomicity and consistency.

Answer 2

You can do this by separating the two statements with a semicolon, e.g. (using the .NET port of SQLite):

using (SQLiteConnection conn = new SQLiteConnection("Data Source=fie.db3"))
{
  conn.Open();

  using (var cmd = conn.CreateCommand())
  {
    cmd.CommandText = "SELECT id FROM fies; DELETE FROM fies WHERE id = 5;";
    using (var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        Console.WriteLine(reader[0]);
      }
    }
  }
}