Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    34

    Unanswered: Update a table with values assigned from another table

    according to title i have a issue wherei have to update a table with values assigned from naother table.
    Here are my requirements

    -We will need you to also write a script that will fix/update the data in DWSODS01.DWT00102_DERV_IMC_MISC. I believe our initial load scripts loaded this table correctly back in June when we created it, but our new etl wasn't maintaining it for legacy (DMS) data feeds. The data is correct in the DWSODS01.DWT00002_IMC_DMS_MAIN table. You can use the following logic in your update statement:

    LAST_SPON_DT_KEY_NO =DWT00002_IMC_DMS_MAIN.INMKT_LAST_SPON_DT_KEY_NO
    FIRST_SPON_DT_KEY_NO =CASE WHEN (DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO = 19000101 or (DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO is null)) then DWT00002_IMC_DMS_MAIN.INMKT_FIRST_SPON_DT_KEY_NO else DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO END


    I have to write a code for one time so that i have to update a table where all column values after june should be updated from another table. Even i have condition for one column also.

    I wrote a code like this

    UPDATE DWSODS01.DWT00102_DERV_IMC_MISC
    Set DWT00102_DERV_IMC_MISC.LAST_SPON_DT_KEY_NO = (SELECT INMKT_LAST_SPON_DT_KEY_NO FROM DWT00002_IMC_DMS_MAIN),
    DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO =
    CASE WHEN ((DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO = 19000101) or (DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO is null)) then
    (SELECT INMKT_FIRST_SPON_DT_KEY_NO FROM DWT00002_IMC_DMS_MAIN)
    else
    (DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO)
    END

    But i am recieving a error saying "a single-row query returns more than one row"
    The problem is that i dont have a where or join condition.
    Whatever data is there in source table should go to target table( only two columns that too one column has a CASE WHEN)

    Can anyone help me?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    UPDATE DWSODS01.DWT00102_DERV_IMC_MISC
    Set DWT00102_DERV_IMC_MISC.LAST_SPON_DT_KEY_NO = (SELECT INMKT_LAST_SPON_DT_KEY_NO FROM DWT00002_IMC_DMS_MAIN), 
    DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO = 
    CASE WHEN ((DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO = 19000101) or (DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO is null)) then 
    (SELECT INMKT_FIRST_SPON_DT_KEY_NO FROM DWT00002_IMC_DMS_MAIN) 
    else 
    (DWT00102_DERV_IMC_MISC.FIRST_SPON_DT_KEY_NO) 
    END
    problem is
    SELECT inmkt_last_spon_dt_key_no ....
    returns more than 1 row
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2009
    Posts
    34
    Thank you for reply

    Okay, I got the problem.
    But i am unable to fix that code.
    can you suggest me in anyway?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But i am unable to fix that code.
    Then that makes 2 of us who can't fix code.

    Either the requirements are flawed or the implementation is flawed.
    I don't know details so can't formulate a solution.

    Good Luck!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Using the merge statement is by far the easiest way to update a table based on the results of another query.

    Oracle Merge SQL Insert Update Upsert

  6. #6
    Join Date
    Nov 2009
    Posts
    34
    The problem to use merge is that i dont have a condition.
    As i said i have to just dump data from one table to another table

    What more details do you need to formulate a solution?
    Just ask me and i will give you
    I already gave all the details i have

  7. #7
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    This seems like a 1 off query and you are not joining anything in the subselect. If its giving you that much trouble, just query the other table to get first sponsor key or what every you need and plug that in to your query.

    Otherwise, you need your subquery to return only 1 row. So add MAX() or rownum = 1.

  8. #8
    Join Date
    Nov 2009
    Posts
    34
    I also have a CASE WHEN for one of the column as i showed above.
    The rows are in 100,000's.
    Is it possible to do as you said

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by rahulsony111 View Post
    I also have a CASE WHEN for one of the column as i showed above.
    The rows are in 100,000's.
    Is it possible to do as you said
    Here is an Oracle Reality Check.
    With Oracle you can issue any DML & Oracle will report how many rows were changed.
    If the row count appears reasonable, you can then issue COMMIT
    If the row count appears wrong, then issue ROLLBACK

    Regardless, no other session will "see" any changed data until after COMMIT occurs.

    So rather than ask us, just issue SQL & see what happens for yourself.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Aug 2009
    Posts
    262
    I smell homework

Posting Permissions

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