更新Firebird中的选定行 [英] Updating selected rows in Firebird

问题描述

我想修改我的查询.现在看起来像这样

I want to modify my query. Right now it looks like this

SELECT EW_POLYLINE.P0_X, EW_POLYLINE.P0_Y, EW_POLYLINE.ID, EW_POLYLINE.STAN_ZMIANY, a.IDE, EW_POLYLINE.ID_WARSTWY
FROM EW_POLYLINE 
LEFT JOIN (
    SELECT EW_OBIEKTY.STATUS
        , EW_OB_ELEMENTY.IDE
        , EW_OB_ELEMENTY.TYP
    FROM EW_OBIEKTY 
    INNER JOIN EW_OB_ELEMENTY 
        ON EW_OBIEKTY.UID = EW_OB_ELEMENTY.UIDO
    WHERE EW_OBIEKTY.STATUS = 0 AND EW_OB_ELEMENTY.TYP <> 1
) as a ON EW_POLYLINE.ID = a.IDE
WHERE EW_POLYLINE.STAN_ZMIANY = 0 AND a.IDE Is Null

现在它像数据库的1/3一样返回，并且我想将这些行的"stan_zmiany"修改为3.因为我无法在Firebird中使用UPDATE FROM构造，所以我尝试了

Right now it returns like 1/3 of database, and I want to modify "stan_zmiany" of these rows to 3. Since I can not use UPDATE FROM construction in Firebird I tried

update EW_POLYLINE
set stan_zmiany = 3
WHERE EXISTS (SELECT 1
FROM EW_POLYLINE 
LEFT JOIN (
    SELECT EW_OBIEKTY.STATUS
        , EW_OB_ELEMENTY.IDE
        , EW_OB_ELEMENTY.TYP
    FROM EW_OBIEKTY 
    INNER JOIN EW_OB_ELEMENTY 
        ON EW_OBIEKTY.UID = EW_OB_ELEMENTY.UIDO
    WHERE EW_OBIEKTY.STATUS = 0 AND EW_OB_ELEMENTY.TYP <> 1
) as a ON EW_POLYLINE.ID = a.IDE
where EW_POLYLINE.STAN_ZMIANY = 0 AND a.IDE Is Null)

但是它更改了所有行的"stan_zmiany"，而不是第一个查询中选择的行，您知道如何解决此问题吗?

but it change "stan_zmiany" for all rows, not for selected in first query, do you know how to fix this?

推荐答案

您存在的子查询是不相关的子查询，这意味着它不依赖于更新记录中的值.

Your sub-query in the exists is an uncorrelated sub-query, which means it doesn't depend on the value in the record under update.

相反，使用

update EW_POLYLINE
set stan_zmiany = 3
where EW_POLYLINE.STAN_ZMIANY = 0
and NOT EXISTS (
    SELECT 1
    FROM EW_OBIEKTY 
    INNER JOIN EW_OB_ELEMENTY 
        ON EW_OBIEKTY.UID = EW_OB_ELEMENTY.UIDO
    WHERE EW_OBIEKTY.STATUS = 0 AND EW_OB_ELEMENTY.TYP <> 1
    AND EW_OB_ELEMENTY.IDE = EW_POLYLINE.ID
)

请注意，请使用not exists而不是exists，因为您实际上是要从EW_POLYLINE更新那些不不能满足该要求的记录.

Notice the use of not exists instead of exists, because you actually want to update the records from EW_POLYLINE that do not fulfill that requirement.

这样，您在子查询中不需要左连接，并且条件EW_POLYLINE.ID = EW_OB_ELEMENTY.IDE使子查询与外部更新语句相关.

This way you don't need the left join in the sub-query, and the condition EW_POLYLINE.ID = EW_OB_ELEMENTY.IDE makes the sub-query correlated to the outer update statement.

还要注意，它的格式与我在我对先前问题的回答中提供的最后一个解决方案中的select语句类似.

Also note that this has a similar form as the select statement in the last solution I provided in my answer to your previous question.

这篇关于更新Firebird中的选定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！