Thanks a lot Helen !





________________________________
From: Helen Borrie <helebor@...>
To: firebird-support@yahoogroups.com
Sent: Fri, July 30, 2010 1:16:03 AM
Subject: Re: [firebird-support] Update with Join


At 09:00 AM 30/07/2010, you wrote:

>Hi ! I was looking over this group to solve my problem but I didn't find the
>answer :(
>I want to update a field on a table using a join,something like this :
>
>UPDATE table1 T1 INNER JOIN table2 T2
> ON T1.fld1 = T2.fld1 and T1.fld2 = T2.fld2
> SET T1.value = 10 WHERE T2.fld3 = 1000
>
>but it gives me
>Invalid token.
>Dynamic SQL Error.
>SQL error code = -104.
>Token unknown - line 1, column 30.
>INNER.

Well, the invalid token is INNER because the parser expects SET. In short,
UPDATE works by targeting a physical table, not an output set.


However, it is possible to do an operation like this using a re-entrant
sub-query involving the object of the update, e.g.,


UPDATE table1 T1
SET T1.value = 10
where exists (
select 1 from table1 T1a
INNER JOIN table2 T2
ON T1a.fld1 = T2.fld1
and T1a.fld2 = T2.fld2
where T2.fld3 = 1000
)

Another approach is to create a view of the target set - which itself is not
naturally updatable - and write an update trigger for it. This achieves the
same thing for the user, while allowing the more familiar high-level SQL
semantics. It requires more work from the developer at the metadata level but
it could be more useful for an app where end-users are composing SQL statements
and you want them to be able to treat logical sets as though they were physical
tables.

./heLen







[Non-text portions of this message have been removed]