title: SQL: Query to find multiple items with a common value in one field
body: <p>I have a DB that is structured like this:</p>
  <pre><code>UserInfo
  ----------
  Id (INT PK AI)
  UserId (INT)
  InfoTypeId (INT FK -&gt; InfoType.Id)
  Value (varchar)
  
  InfoType
  ----------
  Id (INT PK AI)
  InfoTypeName (VARCHAR)
  </code></pre>
  <p>The rows represent information for a given user.  For instance, I may have the following rows in the <code>InfoType</code> table:</p>
  <pre><code>Id | InfoTypeName
  -----------------
  1  | &quot;First Name&quot;
  2  | &quot;Last Name&quot;
  3  | &quot;Age&quot;
  </code></pre>
  <p>and rows in <code>UserInfo</code> like this:</p>
  <pre><code>Id | UserId | InfoTypeId | Value
  --------------------------------
  1  | 1      | 1          | &quot;John&quot;
  2  | 1      | 2          | &quot;Smith&quot;
  3  | 1      | 3          | &quot;20&quot;
  4  | 2      | 1          | &quot;John&quot;
  5  | 2      | 2          | &quot;Doe&quot;
  6  | 2      | 3          | &quot;30&quot;
  7  | 3      | 1          | &quot;Jane&quot;
  8  | 3      | 2          | &quot;Doe&quot;
  9  | 3      | 3          | &quot;25&quot;
  10 | 4      | 1          | &quot;John&quot;
  11 | 4      | 2          | &quot;Smith&quot;
  12 | 4      | 3          | &quot;25&quot;
  </code></pre>
  <p>I want to create a query that deletes all rows from the UserInfo table that refer to a user whose first name is &quot;John&quot; and last name is &quot;Smith&quot;.  This means that I want to delete rows 1, 2, 3, 10, 11, and 12.  I can't figure out how to write the query.</p>
  <p><strong>EDIT:</strong></p>
  <p>I was able to get a list of userIDs with</p>
  <pre><code>SELECT DISTINCT ui1.UserId 
  FROM UserInfo ui1
      INNER JOIN UserInfo ui2 
      ON ui1.UserId = ui2.UserId
  WHERE (ui1.InfoTypeId = 1 AND ui1.Value = &quot;John&quot; AND ui2.InfoTypeId = 2 AND ui2.Value = &quot;Smith&quot;);
  </code></pre>
  <p>And further, a list of rows to delete with</p>
  <pre><code>SELECT * FROM UserInfo ui WHERE ui.UserId IN
      (SELECT DISTINCT ui1.UserId 
      FROM UserInfo ui1
          INNER JOIN UserInfo ui2 
          ON ui1.UserId = ui2.UserId
      WHERE (ui1.InfoTypeId = 1 AND ui1.Value = &quot;John&quot; AND ui2.InfoTypeId = 2 AND ui2.Value = &quot;Smith&quot;));
  </code></pre>
  <p>But when I replace the <code>SELECT</code> with a <code>DELETE</code> as such:</p>
  <pre><code>DELETE FROM UserInfo WHERE UserId IN
      (SELECT DISTINCT ui1.UserId 
      FROM UserInfo ui1
          INNER JOIN UserInfo ui2 
          ON ui1.UserId = ui2.UserId
      WHERE (ui1.InfoTypeId = 1 AND ui1.Value = &quot;John&quot; AND ui2.InfoTypeId = 2 AND ui2.Value = &quot;Smith&quot;));
  </code></pre>
  <p>I get</p>
  <pre><code>SQL Error [1093] [HY000]: You can't specify target table 'UserInfo' for update in FROM clause
  </code></pre>