How do you enable LIMIT for DELETE in SQLite?

limit, sql-delete, sqlite

Solution

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.

Problem

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?

Original source