By user5243421


2009-12-01 07:10:45 8 Comments

Using PHP, I have a simple database that may store multiple items with the same content. I want to delete the first occurrence of an instance when I use DELETE.

How do you enable LIMIT for DELETE in SQLite using PHP?

4 comments

@Chakalaka 2012-10-01 13:12:37

DELETE FROM table WHERE rowid = (SELECT rowid FROM table WHERE condition LIMIT 1 )

@LionKing 2014-12-14 17:46:06

Or: DELETE FROM table WHERE rowid IN (SELECT rowid FROM table WHERE condition LIMIT amount_of_rows)

@IndieBoy 2015-11-15 09:00:42

Worked for me. Thank you

@Andreas 2012-07-20 11:49:19

You can use limit with select and you can combine select and delete like:

DELETE FROM Foo
WHERE someColumn in
(
  SELECT someColumn FROM FOO WHERE SomeCondition LIMIT 200
)

@guneysus 2016-07-21 09:04:13

Clever trick. One more thing: it is safer to use PK column for someColumn.

@intgr 2009-12-01 07:29:56

You cannot enable these options from within PHP, you need to compile SQLite yourself in order to enable these options. Importantly, you need to download the full version, not the amalgamation source release from SQLite download page.

If you're on Unix, get the sqlite-3.6.20.tar.gz tarball and download it. Then:

tar xzf sqlite-3.6.20.tar.gz
cd sqlite-3.6.20
export CFLAGS='-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1'
./configure
make

Then you'll have the sqlite3 command-line utility and the library files in the .libs subdirectory.

@user5243421 2009-12-06 11:30:18

I know you didn't do this, but why was my question locked?

@user767308 2011-05-24 07:39:02

I found it didn't work, the version I use is 3.7.6.2. Any other ideas?

@Nick Dandoulakis 2009-12-01 07:24:09

From here, it seems that you have to compile the SQLite source code using

#define SQLITE_ENABLE_UPDATE_DELETE_LIMIT

in order to enable it.

@user5243421 2009-12-01 07:30:17

Whoops, I should have noted that I'm doing this with php.

Related Questions

Sponsored Content

10 Answered Questions

[SOLVED] Improve INSERT-per-second performance of SQLite?

21 Answered Questions

[SOLVED] How do I check in SQLite whether a table exists?

  • 2009-10-21 14:22:10
  • PoorLuzer
  • 406674 View
  • 824 Score
  • 21 Answer
  • Tags:   sqlite

17 Answered Questions

[SOLVED] SQLite - UPSERT *not* INSERT or REPLACE

  • 2009-01-07 01:47:23
  • Mike Trader
  • 267586 View
  • 495 Score
  • 17 Answer
  • Tags:   sql sqlite upsert

6 Answered Questions

[SOLVED] MySQL LIMIT on DELETE statement

14 Answered Questions

[SOLVED] How do I limit the number of rows returned by an Oracle query after ordering?

17 Answered Questions

10 Answered Questions

[SOLVED] What are the best practices for SQLite on Android?

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

9 Answered Questions

1 Answered Questions

[SOLVED] SQLite delete limit

  • 2012-10-15 12:24:12
  • Woody
  • 458 View
  • 0 Score
  • 1 Answer
  • Tags:   sqlite delete-row

Sponsored Content