using a join and field concatenation

[Ahird001]

Hi,

I am trying to use a join to update a table concatenating some fields together.

update TABLEA set STATUS = (b.TRANSACTION_ID||'Last TRANS was '||b.change_reason_code ||' - ' ||b.master_transaction_status)
where nat_supp_point_text in (select a.nat_supp_point_text from TABLEA a, TABLEB b
where a.nat_supp_point_text = b.nat_supp_point_text
and transaction_id in (select max(transaction_id) from TABLEB x, TABLEA y
where x.nat_supp_point_text = y.nat_supp_point_text and x.nat_supp_point_text = b.nat_supp_point_text))


I have two tables TABLEA AND TABLEB. I want to join both tables on a particular field and then I want to extract the latest record out of TABLEB and update TABLEA using a concatenation of certain TABLEB fields.

I get the statement to work correctly if I just do a simple SET = 'BLAH', but it fails when I try and concatenate fields from tableb with .... invalid indentifier

Any help would be much appreciated.


Actually I've had another go which seems to work -

update TABLEA A set STATUS = (select TRANSACTION_ID||'Last TRANS was '||change_reason_code ||' - ' ||master_transaction_status from TABLEB b
where a.nat_supp_point_text = b.nat_supp_point_text
and transaction_id in (select max(transaction_id) from TABLEB x, TABLEA y
where x.nat_supp_point_text = y.nat_supp_point_text and x.nat_supp_point_text = b.nat_supp_point_text))