Help with insert statement

[matpj]

Hi,
I'm need to write an insert statement on one of my tables.

This is an example of the table:

Proj_ID Text_REF_UID TEXT_FIELD_ID TEXT_VALUE
8192 0 188743734 UKPROJECT1
8192 0 188744007 Fred Bloggs
8192 0 188744011 11.3.2
8192 0 188744008 Product1
7234 0 188743734 UKPROJECT1
7234 0 188744011 11.3.2
7234 0 188744008 Product1

Information about a project is stored acorss several rows in this table, with each row having an ID, which indicates the type of information.
For example, Project 8192, has Fred Bloggs as the project manager (identified by TEXT_FIELD_ID of 188744007)
Project 7234 does not have a project manager associated.


What I need to do, is go through this whole table, and where a Project doesn't have a project manager, insert a row for project manager with a BLANK TEXT_VALUE field.
Does this make sense?

The TEXT_REF_UID field will always be 0, and there can be lots of records for each ProjID.

I am using the following SQL:

INSERT INTO MSP_TEST.MSP_TEXT_FIELDS (PROJ_ID, TEXT_REF_UID, TEXT_FIELD_ID, TEXT_VALUE)
SELECT PROJ_ID, 0, 188744007, ' '
FROM MSP_TEST.MSP_TEXT_FIELDS
WHERE PROJ_ID NOT IN
(SELECT DISTINCT PROJ_ID FROM MSP_TEXT_FIELDS WHERE TEXT_FIELD_ID = 188744007 AND TEXT_REF_UID = 0)

this returns the following error:

ORA-00001: unique constraint (MSP_TEST.I_MSP_TEXT_FIELDS) violated

can anyone tell me what this means and where I am going wrong?

thanks,

Matt
London (UK)

 

[wellster34]

I see the issue, the main query needs to have distinct on the PROJ_ID otherwise for every record where (in your example) that is not 8192...

Proj_ID Text_REF_UID TEXT_FIELD_ID TEXT_VALUE
7234 0 188743734 UKPROJECT1
7234 0 188744011 11.3.2
7234 0 188744008 Product1

It will try to insert the following for every row of 7234.

Proj_ID Text_REF_UID TEXT_FIELD_ID TEXT_VALUE
7234 0 188744007

That is why the unique constraint error is occurring

 

[wellster34]

Try this:

INSERT INTO MSP_TEST.MSP_TEXT_FIELDS (PROJ_ID, TEXT_REF_UID, TEXT_FIELD_ID, TEXT_VALUE)
SELECT DISTINCT(PROJ_ID), 0, 188744007, ' '
FROM MSP_TEST.MSP_TEXT_FIELDS
WHERE PROJ_ID NOT IN
(SELECT DISTINCT PROJ_ID FROM MSP_TEXT_FIELDS WHERE TEXT_FIELD_ID = 188744007 AND TEXT_REF_UID = 0)