I have a table, called "report", which contains report IDs and their respective extension codes. Currently, as an example, the only extension for each ID is 'TXT'. I am trying to insert a new extension ('RTF') into each existing report ID. Here is my code:
merge into report a using (select x.rpt_id as value1, 'RTF' as value2 from report x where x.extension <> 'RTF') b on (a.rpt_id = b.value1) when not matched then insert values (b.value1, b.value2);
I get no errors, but nothing gets inserted...
It would be helpful to post a sample of the data before you run your statement (just a sample, of course) and the desired data after you've run your statement.
It sounds like you are looking for something like
INSERT INTO report( rpt_id, extension ) SELECT rpt_id, 'RTF' FROM report WHERE extension != 'RTF'
If the primary key is actually a composite constraint on the combination of
extension, and your goal is to take your table of N reports each with an extension of
TXT and create N new rows with the same
rpt_id and an extension of `RTF', this will work.
SQL> create table report( 2 rpt_id number, 3 extension varchar2(3), 4 constraint extension_pk primary key( rpt_id, extension ) 5 ); Table created. SQL> insert into report values( 1, 'TXT' ); 1 row created. SQL> insert into report values( 2, 'TXT' ); 1 row created. SQL> insert into report values( 3, 'TXT' ); 1 row created. SQL> insert into report values( 4, 'TXT' ); 1 row created. SQL> insert into report values( 5, 'TXT' ); 1 row created. SQL> select * from report; RPT_ID EXT ---------- --- 1 TXT 2 TXT 3 TXT 4 TXT 5 TXT SQL> insert into report( rpt_id, extension ) 2 select rpt_id, 'RTF' 3 from report 4 where extension != 'RTF'; 5 rows created. SQL> select * from report; RPT_ID EXT ---------- --- 1 RTF 1 TXT 2 RTF 2 TXT 3 RTF 3 TXT 4 RTF 4 TXT 5 RTF 5 TXT 10 rows selected.
Based on the error you are getting, it certainly appears that the primary key is not defined on the combination of
RPT_IDis the primary key of the table meaning that you can't have two rows, one with an extension of RTF and the other with an extension of TXT. But it sounds like that's what you were asking for initially - Justin Cave 2012-04-03 22:38
extension_pkis actually a composite constraint on both columns - Justin Cave 2012-04-03 23:08
INSERTstatements that replicate some problems? If the
EXTENSIONcolumn is part of the primary key, you couldn't insert a
NULLvalue - Justin Cave 2012-04-17 19:23
EXTENSIONis actually part of your primary key, it cannot be NULL (which is what I assume you mean by "do not have the extension"). So something in your description would seem to be off - Justin Cave 2012-04-18 11:39
NOT NULL. That would cause an error but not a violation of the primary key constraint if you tried to set the
NULL. For data, like I did, post the
INSERTstatements that set up the sample data that causes the
MERGEstatement to fail with whatever error you're seeing. Like I did, post a test case that we can run on our systems. - Justin Cave 2012-04-18 11:53