Results 1 to 4 of 4

Thread: Hi guys

  1. #1

    Hi guys

    hi,
    Is it possible to write a update and insert in a single query, i.e., what we do in update strategy transformation. if yes pls give on example or query?


  2. #2
    Junior Member
    Join Date
    Dec 2007
    Answers
    2

    Arrow Re: Hi guys

    Yes we can write update and insert in a single query like

    insert into t1(a, b, c)
    select d, e, f from t2
    on duplicate key update b = e, c = f;

    Use MySQL’s non-standard ON DUPLICATE KEY UPDATE extension to accomplish the insert and update in a single step. This is the fastest method.

    There are other ways to write this statement, for example using the VALUES function, which can help simplify complex queries by referring to the value which would have been inserted into the given column:

    insert into t1(a, b, c)
    select d, e, f from t2
    on duplicate key update b = values(b), c = values(c);

    The disadvantage to this approach is lack of portability, of course. Inserting and updating in a single statement is highly non-standard.


  3. #3
    Junior Member
    Join Date
    Jul 2007
    Answers
    8

    Re: Hi guys

    Hi venkatesh,

    There is one function called Merge using that you can conditionally insert else update the records. If the record exist it will update else it will insert the record. I hope your requirement is this only.
    Below is the syntax for the same.

    merge into
    table_dest d
    using
    table_source s
    on
    (s.id = d.id)
    when matched then update set d.txt = s.txt
    when not matched then insert (id, txt) values (s.id, s.txt);


    Thanks
    Uday


  4. #4
    I am the DON
    Join Date
    Apr 2006
    Answers
    99

    Re: Hi guys

    Quote Originally Posted by udkumar View Post
    Hi venkatesh,

    There is one function called Merge using that you can conditionally insert else update the records. If the record exist it will update else it will insert the record. I hope your requirement is this only.
    Below is the syntax for the same.

    merge into
    table_dest d
    using
    table_source s
    on
    (s.id = d.id)
    when matched then update set d.txt = s.txt
    when not matched then insert (id, txt) values (s.id, s.txt);

    Thanks
    Uday
    @Uday,

    Is the same possible in Oracle 9.2 Version..!
    ________________________________________________________
    And in addition to the above, we can use conditional statements also.

    Sanghala

    If you are satisfied with my reply. Please click “Thanks” button which increases the reputation and can serve you better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact