SQLite update multiple records with different value given by a query

Question

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

Thank you for the edit @CL. That reads much better.

NE5534
– NE5534

2017-05-25 07:27:54 +00:00
Commented May 25, 2017 at 7:27 — NE5534
– NE5534, Commented May 25, 2017 at 7:27

CL. · Accepted Answer · 2017-05-25 07:26:46Z

2

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

answered May 25, 2017 at 7:26

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NE5534 Over a year ago

Thank you @CL. I'll study that a bit more on the train this morning. I didn't know about not using rowid, I was taught that it's better than creating an ID key, but I presume my lecturer wasn't aware that it could change (unless I got the wrong end of the stick).

Collectives™ on Stack Overflow

SQLite update multiple records with different value given by a query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related