Update statement with CTE (Firebird 2.1)

0
votes

I have following statement with which I would like to update table based on some of its columns entry. I use Firebird 2.1 and documentation shows that update can be used with CTEs but my flamerobin stubbornly insists that update statement is not recognized. Can you shed some light on that? 

with cte as (select gen_id(gen_new,1) as num , N.elm_prof, N.elm_mat From 
(select distinct elm_mat, elm_prof from elements ) N )
update elements E set E.PROP_TYPE = cte.num where cte.elm_prof = E.ELM_PROF and cte.elm_mat = E.ELM_MAT

Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 3, column 1
update


merge into elements E1
using (with CTE as (select distinct e2.ELM_MAT mat1, e2.ELM_PROF mat2 from elements e2)
select gen_id(gen_new,1) num, mat1, mat2 from cte)
on E1.elm_mat = mat1 and e1.elm_prof = mat2
when matched then update set e1.prop_type = num
2
sqlfirebirdflamerobin

2 Answers

1
votes

Where in the documentation does it show that update can be used with CTEs? When I look at the Common Table Expressions, it says

<cte-construct>  ::=  <cte-defs>
                      <main-query>

<main-query>     ::=  the main SELECT statement, which can refer to the
                      CTEs defined in the preamble

ie only select is allowed as main-query. I think you're confused by the statement

When enclosed in parentheses, CTE constructs can be used as subqueries in SELECT statements, but also in UPDATEs, MERGEs etc.

later in the doc. As far as I understand this means that you can use statement like

UPDATE elements E
   SET E.PROP_TYPE = (CTE statement here) WHERE...

Note that CTE must be a singelton select in that case.

0
votes

Example 'CTE', it's not quite a CTE but it will help anyone who needs it.

merge into tribcfop t
using(
    with prodncm as (
    select a.iditemproduto, a.idncm, b.codigoncm, a.idtribcfop, c.piscstsai, c.cofinscstsai
    from produtoestoque a
    join ncm b on b.idncm = a.idncm
    join tribcfop c on c.idtribcfop = a.idtribcfop
    where b.codigoncm like '0201%')
    select prodncm.idtribcfop, prodncm.piscstsai, prodncm.cofinscstsai from prodncm
    ) a
on t.idtribcfop = a.idtribcfop
when matched then update set piscstsai = '06', cofinscstsai = '06'