I wonder if anybody could give me a few pointers on a problem I face.
I need to do an UPDATE on table A, from an effective left outer join
on A and another table B. (This is trying to perform a number translation,
where the items that need it are rare.)
The following points *I think* are relevant:
1) The FROM clause in UPDATE should *only* show additional tables,
otherwise I'll get an extra join I didn't want! (IMHO this could do
with being emphasised in the docs.)
2) If a FROM clause is present, but no WHERE clause, a cross join is performed.
3) It is possible to 'emulate' an inner join with statements in a WHERE
clause, but is *not* possible to do so for an outer join. (4.2.2 in the
User's Guide)
If I have the above correct then it seems that there is no way to do
an UPDATE in the way I want in a single statement? I am currently
doing a left outer join into a temporary file, then the UPDATE, but this
involves two joins!
Is there a better way of doing this, or do I have to keep using the
temporary file?
Regards,
Harry.
