CODEWITHSUNDEEP
sqlite
NE5534
NE5534

Reputation: 61

SQLite update multiple records with different value given by a query

My situation is as follows (SQLfiddle):

I have two tables People and Scores and they look like this:

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);

I want to update the People table to contain the rowID of their minScore and maxScore. How do I do that?

AIM:

Terry | 5 | 10
Bob   | 2 | 12

This is what I have tried:

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)

Which just populates the table with the last minimum value.

(I had to use a view because I couldn't get USING to work...)

Upvotes: 1

Views: 2402

Answers (1)

CL.
CL.

Reputation: 180060

Your update uses a scalar subquery; its value

is the first row of the result from the enclosed SELECT statement. In other words, an implied "LIMIT 1" is added to the subquery.

To get different results for different rows, you have to use a correlated subquery:

UPDATE People
SET minScore = (SELECT rowid
                FROM Scores
                WHERE Scores.nameID = People.rowid
                ORDER BY score ASC),
    maxScore = (SELECT rowid
                FROM Scores
                WHERE Scores.nameID = People.rowid
                ORDER BY score DESC);

(And you should not use the implicit rowid for foreign keys; its value can change.)

Upvotes: 2

Related Questions