Updating selected rows in Firebird

Question

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

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)

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

maybe you can use SQL `MERGE` command, read Firebird documentation. // your `update` and `select` statement are not linked. So you say "update E_P rows where some random other EP rows qualify". But this condition is always true. There always are some random rows in E_P that qualify, always. You have to introduce `ALIASES` and link those two queries by their primary key. `update EW_POLYLINE as EP_u ... where exists ( select 1 from EW_POLULINE as EP_s where .... EP_u.PK1=EP_s.PK1 and EP_u.PK2=EP_S.PK2 and ...` // that said, `where exists` might be very slow, especially on "1/3rd of the table" — Arioch 'The, Jul 13 '18 at 08:17
Thank you, `update ew_polyline as E1 set stan_zmiany = 3 WHERE EXISTS (SELECT 1 FROM ew_polyline as E2 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 E2.ID = a.IDE where E1.STAN_ZMIANY = 0 AND a.IDE Is Null and E1.ID = E2.ID)` seems to work perfectly, table modified with `select * from... where` instead of `update` returns same table. — batonico, Jul 13 '18 at 08:45
Can you please check if there's no logic errors? Btw, `where exists` did work well on my table. — batonico, Jul 13 '18 at 08:47
Becouse there's nothing outside `WHERE EXISTS ( )` shouldn't it work same as previously? I mean "update all EW_POLYLINE rows when antyhing qualify in `where exists` command? I know that there's link of primary keys but I don't know how it filters rows to be updated. — batonico, Jul 13 '18 at 09:31
You would have to think about HOW exactly server works to find your data, which approaches scale up and which do not, etc. In particular, you would have to learn to read and understand "query PLANs". In my experience, `where exists` is poorly optimized and would perhaps work slow on big tables, as the data grows with time passing by. Try to fill the table with random data, like few million rows, simulating what your database might look like after few years of work, and then see if the query would still have acceptable speed — Arioch 'The, Jul 13 '18 at 09:53
Here are some translated articles - https://ib-aid.com/en/articles/ - sadly "firebird datapaths" is not one of those, it is in Russian only... — Arioch 'The, Jul 13 '18 at 09:57
`it returns like 1/3 of database` - so, to minimize the FB's work, I would start with that query, then add the `stan_zmiany<>3` condition then wrap it into SQL `MERGE` command to target and change only the rows that need to be. Hard to say without measuring on specific data, but in general when other factors are the same, is should be better to run heavy complex query once and re-run simple coordinated subqueries for its iterations, than run a simple query as a loop body and re-run complex subqueries per iteration. However real *massive data* experiments and reading query PLANs is needed — Arioch 'The, Jul 13 '18 at 10:27
Thank you for such a complex answer. Hypothetically there's 1 row that meets conditions of `select` in `where exists`. Can you please explain me how `update` knows that it should update only this only row and not whole table like first time, when there was no link between between E1 and E2? In both cases - my first solution that did not work and second one that work `where exists` should not return same result (1 row) which will make whole table to be updated? — batonico, Jul 13 '18 at 10:54
Have you looked at https://stackoverflow.com/questions/24800340/update-a-table-with-join as I suggested yesterday? — Mark Rotteveel, Jul 13 '18 at 10:58
Yes @MarkRotteveel, this is why I used `where exists` construction. With help of Arioch 'The I have working query. I just don't get why it works. With my logic `where exists` is returning same result as earlier so it should update all rows in table. I don't know how aliasing same table as 2 different and linking them together made it work since link is inside not outside `where exists` command. — batonico, Jul 13 '18 at 11:07
The problem is that you used an uncorrelated sub-query in the `exists`. Uncorrelated means that it has no condition that depends on the outer update statement. — Mark Rotteveel, Jul 13 '18 at 11:15
@batonico exactly because you introduced the link. Your first query was "update E1 where any random rows (of a kind) exist in E2" - so it was all-or-nothing kind. Either there are some rows of a kind in E2 - and then all rows in E1 would be updated - or there is no rows of a kind in E2 - and then no rows of E1 would be updated. To rephrase your initial logic in Pascal it would be like this: `var flag: Boolean; flag := E2.Exists_Any_Rows_That( condition ); For row in E1 do if flag then E1.update(row);` - that was your request. It either changes all rows in E1 or none of them. — Arioch 'The, Jul 13 '18 at 11:16
You second query was "update E1 rows if there exist a row (of a kind) in E2 with the same ID" - you now check not ever any random row in E2 but the specific E2 row matching the E1 row you decide whether to update or skip. Again, rephrasing in Pascal it would be `var flag: Boolean; For rowE1 in E1 do begin flag := E2.Exists_Specific_Row_That( condition, rowE1.ID ); if flag then E1.update(rowE1); end;` - now you do not ask E2 for any random row but only for a very specific row. But I still think that "MERGE" would be both more straightforward and more efficient — Arioch 'The, Jul 13 '18 at 11:19
"I don't know how aliasing same table as 2 different" - you seems to have a misconception that introducing aliases MADE those tables different? No, they ALREADY WERE different. By explicitly introducing aliases you merely got a method to introduce the linkage between those two already and always different relations (resultsets). `Select` operator does not return you a table, it returns you a resultset. Even if the resultset was picked form one table - it still is not THE table, it is just that, result. — Arioch 'The, Jul 13 '18 at 11:25
in Microsoft SQL this could be solved by extended 3-way switch `MERGE` command. It definitely can not be done with FB2 MERGE Ain't sure about FB3 and the guards added there. — Arioch 'The, Jul 13 '18 at 13:52

Mark Rotteveel · Accepted Answer · 2018-07-13T11:30:56.810

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.

Instead, use

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
)

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.

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.

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.

Uh-oh, not-exists is optimized even worse than exists, in my experience. — Arioch 'The, Jul 13 '18 at 11:27
@Arioch'The I suggest then that you post your own answer that addresses that. — Mark Rotteveel, Jul 13 '18 at 11:28

Updating selected rows in Firebird

1 Answers1

Linked