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

Reply via email to