Hello @ll,
thanks a lot! So many solutions J Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Im Auftrag von Svein Erling Tysvær Gesendet: Mittwoch, 26. Februar 2014 13:00 An: firebird-support@yahoogroups.com Betreff: RE: [firebird-support] CTE >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 ( <http://www.firebirdsql.org/refdocs/langrefupd25-update.html> www.firebirdsql.org/refdocs/langrefupd25-update.html), not the UPDATE statement ( <http://www.firebirdsql.org/refdocs/langrefupd25-update.html> 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
