Re: Can MERGE replace UPDATE/INSERT duo on a single table?

From: Jose Dorado <jose_jesus_dorado_at_yahoo.com>
Date: 7 Apr 2004 07:54:35 -0700
Message-ID: <30a05329.0404070654.560f77ec_at_posting.google.com>

The problem is that the
select * from employee where user_id = 'john123'

returns no rows.

If it helps this works, if user_id is a unique key

merge into employee s
using
(

   select user_id from (

      select user_id from employee where user_id = 'john123'
      union all
      select NULL user_id from dual

   ) where rownum=1
) st
ON (s.user_id = st.user_id)
when matched then

    update set s.pay=50000
when not matched then

    insert (s.user_id, s.pay, s.service_name, s.authorized_for) values     ('john123', 50000, 'foo', 'ALL') Received on Wed Apr 07 2004 - 16:54:35 CEST