title: Merging data from two tables into a third using Oracle MERGE INTO
body: <p>I'm a PHP developer coming from a MySQL background, working with Oracle for the first time.  I'm trying to insert data from two tables into a third, with the functional goal of tracking user permissions to access certain products on my platform, while avoiding UNIQUE CONSTRAINT errors (i.e. adding duplicate records).  I have three tables:</p>
  
  <pre><code>USERS
  PERMISSIONS
  USER_PERMISSIONS
  </code></pre>
  
  <p>And what I want to do is add a row containing the ID from USERS, the ID from PERMISSIONS, and the ID of the current user (so we know who added the record).  Here's my attempt:</p>
  
  <pre><code>MERGE INTO USER_PERMISSIONS up 
      USING (
          SELECT 
              USERS.ID, 
              PERMISSIONS.ID 
          FROM 
              USERS, 
              PERMISSIONS 
          WHERE 
              PERMISSIONS.NAME = 'productalpha'
          AND 
              USERS.SHORTNAME = 'username'
      ) ON (
          USERS.ID = up.USER_ID 
          AND 
          PERMISSIONS.ID = up.PERMISSION_ID
      ) WHEN NOT MATCHED THEN 
      INSERT (
          USER_ID, 
          PERMISSION_ID, 
          LAST_UPDATED_BY_USER_ID
      ) VALUES (
          USERS.ID, 
          PERMISSIONS.ID, 
          '1'  
      );
  </code></pre>
  
  <p>However I'm running into trouble with INVALID IDENTIFIER errors.  I'm aware that the USING (SELECT) portion of the query tells the query what tables I will be using, but I'm not sure how to correctly identify them so that the query works.  Is this the wrong approach?  Any advice would be appreciated.  Thanks much!</p>
  
  <p>EDIT: The exact error message I receive is:  </p>
  
  <pre><code>Error at Command Line : 16 Column : 9  
  Error report -  
  SQL Error: ORA-00904: "PERMISSIONS"."ID": invalid identifier  
  00904. 00000 -  "%s: invalid identifier"  
  </code></pre>