Help with SQL insert statement

[matpj]

Hi,
I'm need to write an insert statement on one of my tables.
It is a linked table from an Oracle Database for Microsoft Project.

This is an example of the table:

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

Can anyone tell me how to do this?
thanks,





Matt
London (UK)

 

[PHV]

You may try something like this:
INSERT INTO tblProjects(ProjID,Text_REF_UID,TEXT_FIELD_ID,TEXT_VALUE)
SELECT ProjID, 0, 188744007, ' '
FROM tblProjects
WHERE ProjID Not In (SELECT ProjID FROM tblProjects WHERE TEXT_FIELD_ID=188744007)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[matpj]

thanks, PH,
I adapted this script to Oracle SQL and tried running it in SQL+, but it returned the following message:

ERROR at line 1:
ORA-00001: unique constraint (MSP_TEST.I_MSP_TEXT_FIELDS) violated


any ideas?
the table is MSP_TEST.MSP_TEXT_FIELDS - which is what I entered, but for some reason the error message has an 'I_' in front of the table name..

or is this a question best posted in the Oracle forum?

Matt
London (UK)