0

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
Sonya Blade
  • 399
  • 7
  • 24

2 Answers2

1

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.

ain
  • 22,394
  • 3
  • 54
  • 74
  • Ok I confused at exactly that statement that you have shown. The problem arises if returned value of CTE is dataset(which will be in my case) rather than singleton, any workaround for that, I'm not restricted to CTE expressions any other option which fullfills update task is wellcome. – Sonya Blade Jan 13 '16 at 07:39
  • I think you could use MERGE statement, see http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd21-merge.html or perhaps just wrap the logic into an stored procedure where you can use various constructs which make the logic easier to understand. – ain Jan 13 '16 at 07:45
  • I also tried to update with merge statement with the code shown at the question. BUt I think that it doesn't accept CTE as subquery statement. Please note also that I use generator, so this might be the cause of discrepancy. – Sonya Blade Jan 13 '16 at 08:02
  • I don't think the generator is the reason it fails. I think it is that you try to refer to the `cte` outside the subselect, AFAIK this can't be done. Error message should state it, no? Please always include the error message! Anyway, I don't have Firebird installed here so I can't test it myself, sorry. – ain Jan 13 '16 at 08:10
  • I added the error message at the bottom of OP, it points the syntax error at the end of CTE expression braces. – Sonya Blade Jan 13 '16 at 09:42
  • ok now I ve updated the working query at the OP now it works, but there is another problem where generator always keeps counting and increasing numbers , I only want it to be limited for CTE expression and not for the outer expressions. – Sonya Blade Jan 13 '16 at 23:08
  • Not sure I understand you but perhaps removing gen_id() call from CTE and using it in update does what you want: `merge into elements E1 using (with CTE as (select distinct e2.ELM_MAT mat1, e2.ELM_PROF mat2 from elements e2) select mat1, mat2 from cte) on E1.elm_mat = mat1 and e1.elm_prof = mat2 when matched then update set e1.prop_type = gen_id(gen_new,1)` – ain Jan 14 '16 at 07:49
  • The problem with this is that: Generator keeps increasing the e1.prop_type values(1.2.3.4.5 and so on ) whenever its called by SQL engine , normally what I want to achieve is if CTE query has returned e.g. two 1, 2 rows then it must update corresponding elements (which are matching with elm_mat, elm_prof) with only 1s and 2s in table. So the purpose of creating CTE is only to autoincrement the distinct values. – Sonya Blade Jan 14 '16 at 08:51
  • So what you actually want is `row_number`? This is not supported by FB2, will be by FB3. You could use some hack to simulate it in FB2 but it would be error prone and hard to follow... I'd wrap the whole thing into an stored procedure instead, there you could use some temp variable to keep track on row_number (ie use `for select` loop and increment the var when updating row). – ain Jan 14 '16 at 09:05
1

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'
Rafael Tabarin Bernardes
  • 11
  • 2