3 Replies Latest reply on Nov 3, 2009 3:23 PM by 21205

    merge statement

    721423
      Can merge statement be used in a single table??
      i mean i want to search for an id in the table and if it is present then i just need to update a few corresponding columns and if that id does not exist then i want to insert a new record.

      Please reply with example.....
        • 1. Re: merge statement
          Frank Kulash
          Hi,

          Yes, you can do that.

          You actually have to SELECT the data from some table. The dual table is handy for this:
          MERGE INTO table_x     dst
          USING  ( SELECT  1          AS id
                       ,      'Foo'          AS name
                FROM      dual
                 )          src
          ON     (src.id  = dst.id)
          WHEN MATCHED
          THEN UPDATE  SET  dst.name  = src.name
          WHEN NOT MATCHED
          THEN INSERT (dst.id, dst.name)
               VALUES (src.id, src.name)
          ;
          Just change the literals 1 and 'Foo' in the src query to whatever you want.
          Of course, you can have any number and type of columns.
          1 person found this helpful
          • 2. Re: merge statement
            721423
            I am using Oracle Developer Suite 10g and i tried to use Merge statement in my code but the MERGE keyword is not recognised the IDE. What could be the solution for this
            • 3. Re: merge statement
              21205
              Bibekananda wrote:
              I am using Oracle Developer Suite 10g and i tried to use Merge statement in my code but the MERGE keyword is not recognised the IDE. What could be the solution for this
              Don't use Developer Suite 10g,... ?
              Just use SQL*Plus or SQL Developer