You have fallen into the double quote trap. SQLite uses double quotes to denote COLUMN NAMES, and single quotes to delimit STRING CONSTANTS.
When asking for "M" or "G", you get the contents of the column named m and g respectively (column names are case insensitive). When asking for "P" or "R", there is no column with that name, so SQLite pretends that you meant to provide a string constant. BTW: You have columns with NUMERIC affinity obviously representing amounts, which will get stored as REAL numbers. Don't do that if you expect to do anything financially relevant. You will incur rounding errors. Instead, store integer values of cents. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Paul Ausbeck Gesendet: Donnerstag, 30. Januar 2020 00:11 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Either a bug or I don't understand SQL update I've been using sqlite for some time but haven't used SQL update until recently. On my first real use, I've encountered a problem that I can't understand at all. To pare this down, I have two tables, citytax and taxitems2, and I've attached a database with just these two tables, total size ~12Kbytes. I perform the following update using the sqlite3 command line interface: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and taxitems2.taxrate = "M"); The result is that the salesm column is set to NULL for all citytax rows. However, I would expect that rows 9 and 22 would be non-NULL. I get the the same all-NULL result for a taxrate of "G" as well, though I would expect that rows 14, 19, and 58 would be non-NULL. The strangest part is that if I specify taxrates of "P", "R", or "", I get the result that I expect. I've included a listing of the taxitems2 table below. As one can see, there are rows where the second column, taxrate, is all of "", "P", "R", "G", and "M". The last column is the amount column, non-null for all rows. I must admit that I don't understand what is going on here. Perhaps even stranger is if I extract the select from the update and give it an appropriate row number, I get the expected non-NULL result: sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and taxitems2.taxrate = "M"; 1176.72 I've included the contents of the taxitems2 table as well as the schema for tables taxitems2 and citytax below. One the one hand, this seems almost certainly to be a bug, but on the other, it is so basic, that I can't believe that I'm the first to encounter it. Any assistance that anyone can provide would of course be much appreciated. I'm using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both environments behave the same in this regard. As I was writing I had yet another test idea: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 77); /* taxrate = "M" */ and update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 71); /* taxrate = "G" */ Both of these updates perform as expected. Regards, Paul Ausbeck sqlite> select * from taxitems2; WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5 SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0 USX,P,58,14,1,0,0,0,0,0,0,7104.0 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 USX,P,58,14,1,0,0,0,0,0,0,5446.4 USX,P,58,14,1,0,0,0,0,0,0,1657.6 AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3 PEM,G,58,10,2,0,0,0.75,2,1.5,4,8 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 HVY,R,58,14,1,0,0,0,0,0,0,72.5 HVY,R,58,14,1,0,0,0,0,0,0,176.4 VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,168 CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2 CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,65 WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9 WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4 WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72 WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3 ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394 DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4 DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8 DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7 sqlite> .schema taxitems2 CREATE TABLE taxitems2( code TEXT, taxrate TEXT, citynum INT, countynum INT, statenum INT, citym NUM, cityg NUM, countym NUM, countyg NUM, statem NUM, stateg NUM, amount ); sqlite> .schema citytax CREATE TABLE citytax( number INT, code INT, g NUM, m NUM, comment TEXT, taxg NUM, taxm NUM, salesg NUM, salesm NUM, salesr NUM, salesp NUM, stamp INT ); _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
