Results 1 to 2 of 2
  1. #1
    Join Date
    May 2014
    Posts
    4

    Unanswered: Need Help with Update Statement

    Hi Guys,

    I need one help with a update statement.

    Using the below query, I need to update one table Tab_Upd.

    Code:
    select a.instrument ins_new, b.instrument_id ins_old, deal_no deal
    from Tab_Src a, Tab_Src b
    where a.deal_no = b.deal_no
    and a.instrument <> b.instrument
    In table Tab_Upd, there is a deal_no column and Instrument Column.

    I need to update instrument column with ins_old value and in the where clause i have to give condition on Deal_no=deal and instrument id is new.

    I tried the below but it didnt work out.

    Code:
    Update Tab_Upd a set a.instrument = (select b.ins_old from (
    select a.instrument ins_new, b.instrument_id ins_old, deal_no deal
    from Tab_Src a, Tab_Src b
    where a.deal_no = b.deal_no
    and a.instrument <> b.instrument) b
    where a.instrument = b.ins_new and a.deal_no = b.deal
    Cheers!!!

  2. #2
    Join Date
    Mar 2007
    Posts
    628
    Hi,

    unfortunately, for me "does not work" even the query you are using - in the SELECT clause the non-existent column INSTRUMENT_ID is present and the column DEAL_NO is not aliased.

    After fixing it, I get:
    Code:
    -- sample data
    with tab_src(deal_no, instrument) as ( select 1, 11 from dual
                                 union all select 1, 12 from dual )
    -- fixed query
    select a.instrument ins_new, b.instrument ins_old, a.deal_no deal
    from tab_src a, tab_src b
    where a.deal_no = b.deal_no
    and a.instrument <> b.instrument
    ;
       INS_NEW    INS_OLD       DEAL
    ---------- ---------- ----------
            12         11          1
            11         12          1
    So, when seeing the result set (yes, you will get 2 rows for 2 rows with the same DEAL_NO and different INSTRUMENT), it is totally unclear what is "ins_old value" supposed to be.

    Maybe you should take one step back and try to exactly what you want to actually achieve.

Posting Permissions

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