Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: Update query with From clause issue

    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
    Last edited by testing121; 09-22-11 at 09:16.

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    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:
    Code:
    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
    #          ^
    #  217: Column (party) not found in any table in the query (or SLV is undefined).
    #
    and alt_comm.cust_id = party.party_cust_id
    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:
    Code:
    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 cust_id in ( select party.party_acct_id from party,temp_table where party.party_acct_id = temp_table.account_id )
    Hope this helps
    Eric

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •