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