On Friday, 15 November, 2019 15:22, Gan Uesli Starling <g...@starling.us> wrote:
>In the following update query, I had expected for the integer values >"rowid" from the table "info" to project copies of themselves singly and >separately into the integer cells "info_id" of table "qso", row-by-row, >where the timestamps "t_from" of each are matching. >UPDATE qso SET info_id = ( > SELECT info.rowid FROM info, qso WHERE info.t_from = qso.t_from >) >WHERE qso.t_from = info.t_from; That statement is in error and cannot possibly compile. Column "info.t_from" in the outer update does not exist. The outer update may only reference columns contained in the table being updated, qso. Furthermore, the "subselect" returns multiple rows and is not correlated with the data you are updating. >They do not. Instead what happens, is that the integer value "rowid" >from table "info" of first row in which the timestamps "t_from" of table >"qso" and table "info" are matching goes multiply into all cells >"info_id" of table "qso". As I said, I think you are mistaken. The above statement does nothing at all since it is an error and cannot execute. >How can I instead get what I want? UPDATE qso SET info_id = (SELECT rowid FROM info WHERE t_from == qso.t_from) WHERE EXISTS (SELECT 1 FROM info WHERE t_from == qso.t_from); The where exists prevents setting info_id to null where no match is found in the correlated subquery. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users