Results 1 to 4 of 4

Thread: Error in UPDATE

  1. #1
    Join Date
    Nov 2009
    Posts
    34

    Unanswered: Error in UPDATE

    I have a scenario where i have to update 2 columns in a table form another table
    I wrote the code as below

    UPDATE DWSODS01.DWT00102_DERV_IMC_MISC
    Set DWT00102_DERV_IMC_MISC.LAST_SPON_DT_KEY_NO =
    (SELECT LAST_SPONSOR_KEY_NO
    FROM TABLE2 ),
    DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO =
    (SELECT FIRST_SPONSOR_KEY_NO FROM TABLE2)
    WHERE DWT00102_DERV_IMC_MISC.IMC_KEY_NO = TABLE2.IMC_KEY_NO

    I am getting an error
    "Table2.IMC_KEY_NO : invalid identifier"

    I know why is the error coming but i am unable to find a solution for it.
    Please help

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Your main problem is, that the sub-selects will return more than one row (especially the first one) and that will give you an error ("single-row subquery returns more than one row")

    You need to make sure that your subselect SELECT LAST_SPONSOR_KEY_NO FROM TABLE2 returns exactly one row. Either by using an aggregate function (min(), max(), ...) or by ensuring that you select from that table based on the primary key.

    And you only need a single sub-select if the values for both columns are coming from the same row in TABLE2

    So the statement might look like this
    Code:
    UPDATE dwt00102_derv_imc_misc
      SET (last_spon_dt_key_no, first_spon_dt_key_no) = 
              (SELECT last_sponsor_key_no, 
                      first_sponsor_key_no
               FROM table2  
               WHERE imc_key_no = table2.imc_key_no);
    This assumes that imc_key_no is the primary key in table2!

  3. #3
    Join Date
    Nov 2009
    Posts
    34
    Thanks for the reply and solution

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    UPDATE dwt00102_derv_imc_misc x
      SET (last_spon_dt_key_no, first_spon_dt_key_no) = 
              (SELECT last_sponsor_key_no, 
                      first_sponsor_key_no
               FROM table2  
               WHERE x.imc_key_no = table2.imc_key_no);
    don't forget the alias on the updating table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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