title: SQLite update multiple records with different value given by a query
body: <p>My situation is as follows (<a href="http://sqlfiddle.com/#!5/b5a7a/1" rel="nofollow noreferrer">SQLfiddle</a>):</p>
  
  <p>I have two tables <code>People</code> and <code>Scores</code> and they look like this:</p>
  
  <pre><code>CREATE TABLE People 
  (
      name TEXT,
      minScore INTEGER,
      maxScore INTEGER,
      FOREIGN KEY (minScore) REFERENCES B(rowid),
      FOREIGN KEY (maxScore) REFERENCES B(rowid)
  );
  
  INSERT INTO People (name) VALUES ('Terry');
  INSERT INTO People (name) VALUES ('Bob');
  
  CREATE TABLE Scores  
  (
      nameID INTEGER,
      score INTEGER,
      FOREIGN KEY (nameID) REFERENCES A(rowid)
  );
  
  INSERT INTO Scores (nameID, score) VALUES (1, 10);
  INSERT INTO Scores (nameID, score) VALUES (1, 8);
  INSERT INTO Scores (nameID, score) VALUES (1, 5);
  INSERT INTO Scores (nameID, score) VALUES (2, 12);
  INSERT INTO Scores (nameID, score) VALUES (2, 8);
  INSERT INTO Scores (nameID, score) VALUES (2, 2);
  </code></pre>
  
  <p>I want to update the <code>People</code> table to contain the <code>rowID</code> of their <code>minScore</code> and <code>maxScore</code>. How do I do that?</p>
  
  <p>AIM:</p>
  
  <pre><code>Terry | 5 | 10
  Bob   | 2 | 12
  </code></pre>
  
  <p>This is what I have tried:</p>
  
  <pre><code>CREATE VIEW minScores 
  AS 
      SELECT Scores.rowid AS scoreID, Scores.nameID AS nameID 
      FROM Scores 
      WHERE score IN (SELECT MIN(score) 
                      FROM Scores AS Scores2 
                      GROUP BY Scores2.nameID);
  
  UPDATE People
  SET minScore = (SELECT scoreID FROM minScores) 
  WHERE People.rowid IN (SELECT nameID FROM minScores)
  </code></pre>
  
  <p>Which just populates the table with the last minimum value.</p>
  
  <p>(I had to use a view because I couldn't get USING to work...)</p>