title: SQL Delete statement with nested selects
body: <p>I am trying to make a subquery in a <code>DELETE</code> statement in order to improve performance. The plain <code>DELETE</code> statement works but the subquery ones are either deleting all rows indiscriminately, or only one row per invocation. I am confused as to why the statements are not equivalent.</p>
  <p>Jump down to &quot;What doesn't work&quot; to see the problem statements.</p>
  <h3>Upfront info</h3>
  <p>I am using <code>sqlite3</code> from <code>python2</code> to manage a database of pictures with associated tags.</p>
  <p>The schema for the table is:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE VIRTUAL TABLE &quot;files&quot; USING fts3(fname TEXT, orig_name TEXT, tags TEXT, md5sum TEXT);
  </code></pre>
  <p>Tags are organized as a comma separated list so direct string comparison in <code>sqlite</code> doesn't (easily) work, so I've add a helper function <code>TAGMATCH</code></p>
  <pre><code>def tag_match(tags, m):
      i = int(m in [i.strip() for i in tags.split(',')])
      return i
  db.create_function('TAGMATCH', 2, tag_match)
  </code></pre>
  <hr />
  <h3>What works</h3>
  <p>This does what I want/expect. It deletes all rows where the column <code>tags</code> contains the tag <code>'DELETE'</code>. Downside is, as far as I understand, this requires a linear scan of the table. Because of the &quot;dangers&quot; of deleting something from the table I did want to use <code>MATCH</code> in case, in some hypothetical situation, a match occurs with another unintended tag ie. <code>'DO NOT DELETE THIS'</code>.</p>
  <pre class="lang-sql prettyprint-override"><code>DELETE FROM files WHERE TAGMATCH(tags, 'DELETE')
  </code></pre>
  <hr />
  <hr />
  <h3>What doesn't work</h3>
  <p>To speed things up I tried a trick I read in another stackoverflow post where <code>MATCH</code> is used to narrow down the search, then a direct string comparison is done on those results ie</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM (SELECT * FROM table WHERE words MATCH keyword) WHERE words = keyword
  </code></pre>
  <hr />
  <p>I tried using this trick here, but instead it deletes every row in the table.</p>
  <pre class="lang-sql prettyprint-override"><code>DELETE FROM files WHERE TAGMATCH((
          SELECT tags FROM files WHERE tags MATCH 'DELETE'), 'DELETE')
  </code></pre>
  <hr />
  <p>This is what I first came up with. I realize now it's not a particularly good solution, but since its effect puzzles me I'm including it. This statement only deletes one row containing the tag <code>'DELETE'</code>. If invoked again, it deletes another row, and so on until all the rows with <code>'DELETE'</code> are removed:</p>
  <pre class="lang-sql prettyprint-override"><code>DELETE FROM files WHERE rowid = (
          SELECT rowid FROM (
              SELECT rowid, tags FROM files WHERE tags MATCH 'DELETE')
      WHERE TAGMATCH(tags, 'DELETE'))
  </code></pre>