Not inserting new records

Go To StackoverFlow.com

1

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...

2012-04-03 22:23
by cloudcalculus


2

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 rpt_id and 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_id and extension.

2012-04-03 22:27
by Justin Cave
Hi there, thank you for your reply. I have tried the above but I get the error "unique constraint violation (extensionpk)". Is this enough sample data? rptid = r00001, r00002, r00003, r00004, r00005 extension (all of them) = 'TXT - cloudcalculus 2012-04-03 22:36
@racer - And what do you want the end result to be in the table? If you start with 5 rows, do you want to end with 10 rows? If so, then based on the error, I'm guessing that RPT_ID is 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
Yes, that is I want, but how it works is that the Primary Key is the pair of rpt_id and extension - cloudcalculus 2012-04-03 22:57
@racer - If the primary key is defined as you say, this will work. I just updated my answer to include a demonstration. From the error you posted, it does not appear that extension_pk is actually a composite constraint on both columns - Justin Cave 2012-04-03 23:08
Thanks, Justin, it did work - cloudcalculus 2012-04-04 12:46
I just found something... when the reports either all have or do not have the extension, it works, but when at least one report is missing the extension, I get the PK constraint violation... Would you be able to figure out why that is so - cloudcalculus 2012-04-17 18:15
@racer - Can you post the table definition you're using and some INSERT statements that replicate some problems? If the EXTENSION column is part of the primary key, you couldn't insert a NULL value - Justin Cave 2012-04-17 19:23
Hi, what do you mean by table definition - cloudcalculus 2012-04-18 11:35
@racer - Similar to what I did in my answer, post the DDL to create your table (the table definition) or, even better, a simpler table definition that replicates the problem. Then post the DML to insert a few rows that replicate the problem. As I said, if EXTENSION is 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
SQL> create table report( 2 rptid number, 3 extension varchar2(3), 4 constraint reportpk primary key( rptid, extension ) 5 constraint reportfk1 foreign key (extension) constraint reportfk2 foreign key (rptid) - cloudcalculus 2012-04-18 11:47
merge into report b using (select rptid, extension from report a where not exists (select * from report x where x.rptid = a.rptid and x.extension = 'PDF')) c on (b.rptid = c.rptid and c.extension = 'PDF') when not matched then insert (b.rptid, b.extension) values (c.rpt_id, 'PDF') - cloudcalculus 2012-04-18 11:49
Do you still need more info - cloudcalculus 2012-04-18 11:49
>racer - First, what do you mean by "do not have the extension"? Do you mean that the extension column is NULL? The primary key constraint automatically makes the EXTENSION column NOT NULL. That would cause an error but not a violation of the primary key constraint if you tried to set the EXTENSION column to NULL. For data, like I did, post the INSERT statements that set up the sample data that causes the MERGE statement 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
@racer - Amd please edit your question to include all this new information-- you don't want people to have to read through all the comments in order to determine what you're asking - Justin Cave 2012-04-18 11:54