CODEWITHSUNDEEP
sqlsqlite
leon
leon

Reputation: 1512

SQLite: Combine SELECT and DELETE in one statement

I need to run two statements like so:

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

Currently I prepare and run two different statements. I wonder if there is a way to combine it in one statement.

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

Upvotes: 10

Views: 14351

Answers (5)

gerardnico
gerardnico

Reputation: 1073

If you just want to select rows and delete them in one pass, you can use the returning clause in the delete statement.

For instance:

delete from myTable returning *

The delete statement has all select functionalities possible such as with and where that permits to select rows with any logic.

Upvotes: 11

Bhavin Patel
Bhavin Patel

Reputation: 912

It's late but for future visitor. In my case I did like below

I want to delete that ID where amount = 5 so I did this

public void selectAndDelete() {


        try {
            SQLiteDatabase db = this.getWritableDatabase();
            int i=db.delete(table_name, "ID = (select ID from table_name where amount = 5)", null);

            if(i>0)
            {
                    // I'm inserting here new record
            }
        } catch (SQLException e) {

        }
    }

So, In your case you need to modify where condition like you want

Upvotes: 0

mwag
mwag

Reputation: 4035

Assuming that your calling thread/process has a unique identifier (e.g. thread_id), I think a viable approach would be to add a flag (say, "handlerid") to your table, which is set to null on insert, and then do:

update db set handlerid = <my_thread_id> where handlerid is null;
select * from db where handlerid is not null and handlerid = <my_thread_id>;
delete from db where handlerid is not null and handlerid = <my_thread_id>;

Not sure how it would perform vs a transaction but can't think of any reason it would be materially worse (might even be better), and using this approach the code seems about as straightforward as you can get. unlike a transaction, it won't require you to loop in the case that a transaction fails, in order to be sure that all outstanding elements that were in the table at the time of the most recent SELECT got processed.

Upvotes: 1

itowlson
itowlson

Reputation: 74802

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]);
      }
    }
  }
}

Upvotes: -2

Alex Martelli
Alex Martelli

Reputation: 881565

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.

Upvotes: 14

Related Questions