Hello Folks,

I am trying to update a table with email address from a source table. The source and the destination tables are linked via a mapping table. The table's are shown below:

alt_comm (Destination table)
1 type
2 infoEmailAddress
3 cust_id

party (mapping table)
1 party_cust_id
2 party_acct_id

temp_table (source table)
1 account_id
2 emailAddress

I have written a query which will update this email address in the infoEmailAddress column of "alt_comm" table based on the account_id, but when I run this query it gives me a syntax error.

update alt_comm
set type=’E’, infoEmailAddress = (Select temp_table.emailAddress
from temp_table INNER JOIN party
On party.party_acct_id = temp_table.account_id
INNER JOIN alt_comm
On alt_comm.cust_id = party.party_cust_id)
where party.party_acct_id = temp_table.account_id
and alt_comm.cust_id = party.party_cust_id


Please could you review the query and highlight where I have gone wrong.

Would be really grateful if you could help?

Thanks

Hi,

two things:
1) you talk about a syntax error but you do not show where it is, which would be of great help :-). If you run the query from dbaccess, after running it a getting the error, choose the option 'U' ( use editor ). You will be shown the text of the error code and the place of the error in the query text, indicated by the '^' character. You should check the version of your engine, because I am not convinced this type of complex multi-table syntax is accepted in Update for older versions. I'd have to check the release notes.

2) I am running your query in IDS 11.70FC3 IE. I have no syntax error, but an expectable error following:
Your update is on the alt_comm table, but you use a where clause invoking 2 tables that are not in the main update statement tables list. In my knowledge, UPDATE on more than one table is not allowed, at least with IDS. So it won't work.
Not sure about what you want to do, but you will have to reformulate your query, probably something like the following, which at least works:
Hope this helps
Eric