CODEWITHSUNDEEP
sqlsqlite
afarah
afarah

Reputation: 783

Sqlite update not affecting all rows when using subquery

Based on this answer I am trying to update a column in table b based on values from table a, where table b has a foreign key to table a. My query executes successfully and works as expected on the rows it affects. However, it does not affect all rows, and I do not understand why. It seems to only affect three rows no matter how large the database.

Here is a reproducible example:

CREATE TABLE a ( qty1 INTEGER, qty2 INTEGER, aID INTEGER PRIMARY KEY)WITHOUT ROWID;

CREATE TABLE b ( bID INTEGER, aID INTEGER,FOREIGN KEY(aID) REFERENCES a(aID) ON DELETE CASCADE);

INSERT INTO a (qty1, qty2, aID) VALUES (0, 100, 1906250435150010797);

INSERT INTO a (qty1, qty2, aID) VALUES (100, 100, 1906250435150010798);

INSERT INTO a (qty1, qty2, aID) VALUES (0, 100, 1906250435150010799);

INSERT INTO b (bID, aID) VALUES (0, 1906250435150010797);

INSERT INTO b (bID, aID) VALUES (0, 1906250435150010798);

INSERT INTO b (bID, aID) VALUES (1, 1906250435150010798);

INSERT INTO b (bID, aID) VALUES (0, 1906250435150010799);

ALTER TABLE b ADD COLUMN SameQty INTEGER;

UPDATE b SET SameQty = 0 WHERE aID = (SELECT aID from a where qty1 != qty2);

UPDATE b SET SameQty = 1 WHERE aID = (SELECT aID from a where qty1 = qty2);

Expected result:

enter image description here

Actual result:

enter image description here

Tested on sqlite 3.26.0

Upvotes: 1

Views: 68

Answers (2)

sticky bit
sticky bit

Reputation: 37482

Your using a subquery that can and does return more than one row in a scalar context when doing "WHERE aID = (SELECT aID from a". Use IN instead of =.

UPDATE b SET SameQty = 0 WHERE aID IN (SELECT aID from a where qty1 != qty2);
UPDATE b SET SameQty = 1 WHERE aID IN (SELECT aID from a where qty1 = qty2);

db<>fiddle

Upvotes: 2

Shawn
Shawn

Reputation: 52549

Your update statement: 

UPDATE b SET SameQty = 0 WHERE aID = (SELECT aID from a where qty1 != qty2);

has a subquery that can return multiple rows, but is used in a context where only a single row is expected. I feel like that should cause an error, but if you're not seeing one, maybe it's just picking a single arbitrary row from the results?

Anyways, you want IN:

UPDATE b SET SameQty = 0 WHERE aID IN (SELECT aID from a where qty1 != qty2);

Upvotes: 2

Related Questions