How to delete rows in sqlite with a WHERE clause that references multiple tables?

Question

Given the following tables:

acl (acl_id integer, name character)
ace (acl_id integer, users_id integer)
users (users_id integer, login character)

And some sample data:

acl (0, 'view') (1, 'sell'), (2, 'void')
ace (0, 9), (1, 9), (2, 9), (0, 8)
users (8, 'joe') (9, 'john')

I attempted the following:

DELETE FROM ace
  WHERE ace.acl_id = acl.acl_id AND acl.name = ?
    AND ace.users_id = users.users_id AND users.login = ?

but this fails in sqlite3_prepare_v2() with SQLITE_ERROR "no such column: acl.acl_id" (which isn't very helpful)

I get the same result with:

DELETE FROM ace
  WHERE acl_id = acl.acl_id AND acl.name = ?
    AND users_id = users.users_id AND users.login = ?

Thankfully, the scary:

DELETE FROM ace,acl,users
  WHERE ace.acl_id = acl.acl_id AND acl.name = ?
    AND ace.users_id = users.users_id AND users.login = ?

fails with a syntax error on the first comma.

If I got far enough to bind the values 'sell' and 'john' to the parameters, and to step the query, then I would expect the (1, 9) row of the ace table to be deleted.

Is this possible to accomplish without doing two SELECTs first to get the acl_id and users_id values?

You need to JOIN the tables. See [this question](https://stackoverflow.com/q/8598791/62576) and its answer for an example. — Ken White, May 16 '19 at 00:16
Delete in with inner joins is tricky in sqlite (SQLite doesn't support direct syntax for this)... this is a duplicate of https://stackoverflow.com/questions/24511153/how-delete-table-inner-join-with-other-table-in-sqlite — Ahmed Masud, May 16 '19 at 00:36
Possible duplicate of [How delete table inner join with other table in Sqlite?](https://stackoverflow.com/questions/24511153/how-delete-table-inner-join-with-other-table-in-sqlite) — Ahmed Masud, May 16 '19 at 00:37
@JohnHascall: That's why I didn't post an answer. :-) I don't have SQLite on this machine to test it. — Ken White, May 16 '19 at 00:47
@AhmedMasud I have no idea if that Q/A applies to me because the syntax for SQL is so freaking baroque (how I pine for the days of Quel). Do I need two SELECTS (one each for acl_aid and users_id)?? — John Hascall, May 16 '19 at 00:47

score 0 · Answer 1 · answered May 16 '19 at 01:41

With some help from linked Q/A in the comments, I was able to come up with what seems like a functional query:

DELETE FROM ace
 WHERE acl_id = (
       SELECT acl_id
         FROM acl
        WHERE name = ?
   )
   AND users_id = (
       SELECT users_id
         FROM users
        WHERE login = ?
   )

and I have to say, that's just ridiculous.

"SQL, the EBCDIC of query languages"

How to delete rows in sqlite with a WHERE clause that references multiple tables?

1 Answers1