>Hello,

>
>I get an error message during executing the following statement:
>
>with mat as (
>select  a.teilenr, c.vkpreis, a.matteilenr, c.bezeichnung from tmaterial a left join tteile c on a.teilenr = c.teilenr
>where a.kundennr = 24823 )
>update tteile set tteile.minvk = mat.vkpreis * 0.90 where teilenr = mat.teilenr
>
>I know, I can do the following (update XX where (select.)
>
>Why complain firebird the update..?

Very simple, CTEs are defined as part of the SELECT statement (www.firebirdsql.org/refdocs/langrefupd25-update.html), not the UPDATE statement (www.firebirdsql.org/refdocs/langrefupd25-update.html). Though, I do notice there's no syntax definition for SELECT the same way as there are for UPDATE even though the examples are helpful.

I would say another way to solve your case, is to use something like:

execute block as
declare variable teilenr integer;
declare variable vkpreis integer;
begin
for select a.teilenr, c.vkpreis from tmaterial a left join tteile c on a.teilenr = c.teilenr where a.kundennr = 24823)
into :teilenr, :vkpreis do
begin
update tteile set minvk = :vkpreis * 0.90 where teilenr = :teilenr;
end
end

At first sight your update statement may seem like a good way to write things. However, you're not allowed to use tables the way you use them, i.e. you couldn't write

update tteile set tteile.minvk = mat.vkpreis * 0.90 where teilenr = mat.teilenr

if mat had been a table.

Rather, you would have to write

update tteile set tteile.minvk = (select vkpreis * 0.90 from mat where tteile.tteilenr = mat.teilenr)
where exists(select * from mat where tteile.teilenr = mat.teilenr)

and that is not all too different from what I think you can do today:

update tteile set tteile.minvk = (with mat as (select c.vkpreis, from tmaterial a
join tteile c on a.teilenr = c.teilenr
where a.kundennr = 24823)
select mat.vkpreis * 0.90)
where exists(with mat as (select a.teilenr from tmaterial where kundennr = 24823)
select * from mat where teilenr = mat.teilenr)

Though, of course, I wouldn't mind if some kind of update shortcut was available, e.g.

with mat as (select a.teilenr, c.vkpreis, a.matteilenr, c.bezeichnung
from tmaterial a
left join tteile c on a.teilenr = c.teilenr
where a.kundennr = 24823)
update tteile
join mat on tteile.teilenr = mat.teilenr
set tteile.minvk = mat.vkpreis * 0.90

(of course only updating the table before the join and requiring either singular joins or using aggregate results - e.g. sum(mat.vkpreis))

Note that update...join is just what comes to my mind now, I almost exclusively use Firebird and am almost certain there are better ways of implementing something similar (as well as good reasons for not implementing it).

Set