All these issues occur when the fields used in joins are not specified to be unique via indexes. This creates an N to M matching which obviously raises the question during writes to any table - which specific value out of N or M potential values do you want? The moment we add an index it all becomes easy. Ie, in my example for lines vs points above, suppose we specify that line IDs (v) are unique: --SQL9 CREATE TABLE lines (mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id), v INT32, name NVARCHAR, INDEX v_x BTREE (v)); INSERT INTO lines (v) VALUES (1), (2), (3); CREATE TABLE points (mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id), v INT32, name NVARCHAR); INSERT INTO points (v, name) VALUES (1, 'a'), (1, 'b'), (2, 'c'), (4, 'd'); Then this query that composes a name for each line out of the names of its points carries that uniqueness over to the result table: --SQL9 SELECT lines.v, Max(points.name) AS p_name FROM lines INNER JOIN points ON lines.v=points.v GROUP BY lines.v; ...check the result table and you will see the index on lines.v. And the only reason we can't update that just yet is that the result table does not contain the target field we want to write to. You have to join it by running a query on top of this one: --SQL9 SELECT lines.v, lines.name, computed.p_name FROM lines INNER JOIN ( SELECT lines.v, Max(points.name) AS p_name FROM lines INNER JOIN points ON lines.v=points.v GROUP BY lines.v ) AS computed ON lines.v=computed.v; And that can be updated directly: --SQL9 UPDATE ( SELECT lines.v, lines.name, computed.p_name FROM lines INNER JOIN ( SELECT lines.v, Max(points.name) AS p_name FROM lines INNER JOIN points ON lines.v=points.v GROUP BY lines.v ) AS computed ON lines.v=computed.v ) SET name=p_name; No functions and no big tricks. OK, maybe bringing fields to update via a second SELECT can qualify as a trick. But it doesn't seem too big.
|