Sqlite Update columns

我的表中有5列：id、x、y、source、distance。

对于每个id，我想找到最接近的x&y值，并更新距离字段中的该值及其在源中的id。

我可以通过以下查询更新距离：

UPDATE item SET distance = ( SELECT MIN( ABS (a.x - item.x) + ABS (a.y - item.y) ) 
FROM item AS a WHERE a.id != item.id )

但是，我无法更新源代码，因为sqlite不支持从单个选择中进行多列更新。

当我尝试将查询放入where条件时，我会得到错误，没有item.x 这样的列

UPDATE item SET link = ( SELECT id FROM item AS a WHERE a.id != item.id 
ORDER BY ABS (a.x - item.x) + ABS (a.y - item.y) LIMIT 1 )

如何解决我的查询？

显然，SQLite不允许在ORDERBY子句中引用外部表。

您可以通过将距离计算添加到SELECT子句来解决此问题。这需要围绕它包装另一个子查询，以便只返回一列：

 UPDATE item
 SET link = ( SELECT id
              FROM ( SELECT id,
                            ABS (a.x - item.x) + ABS (a.y - item.y)
                     FROM item AS a
                     WHERE a.id != item.id
                     ORDER BY 2
                     LIMIT 1 ))

如果您首先更新link列，则可以使用该值来计算距离，而无需再次搜索整个表：

UPDATE item
SET distance = ( SELECT ABS (a.x - item.x) + ABS (a.y - item.y)
                 FROM item AS a
                 WHERE a.id = item.link )

首先，您可以使用以下逻辑为每条记录获取最接近的id：

select i.*,
       (select id
        from item i2
        where i2.id <> i.id
        order by abs(i2.x - i.x) + abs(i2.y - i.y)
       ) as closest_id
from item i;

您可以将其放入with子句中，以简化查询的其余部分：

with closest as (
      select i.*,
             (select id
              from item i2
              where i2.id <> i.id
              order by abs(i2.x - i.x) + abs(i2.y - i.y)
             ) as closest_id
      from item i
     )
update item
    set link = (select closest_id from closest where closest.id = item.id),
        distance = (select abs(i2.x - c.x) + abs(i2.y - c.y)
                    from closest c join
                         item i2
                         on c.closest_id = i2.id
                    where closest.id = item.id
                   );

编辑：

如果with不可用，则可以在没有with的情况下执行此操作，只需多次使用子查询即可。然而，我建议两个更新：

update item
    set link = (select id
                from item i2
                where i2.id <> item.id
                order by abs(i2.x - item.x) + abs(i2.y - item.y)
               );
update item
    set distance = (select abs(i2.x - item.x) + abs(i2.y - item.y) 
                    from item i2
                    where i2.id = item.link
                   );

相关内容

最新更新

热门标签：