On Friday, 15 November, 2019 15:22, Gan Uesli Starling <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
