>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
