| insert statement w/o having unique constraint error [message #635304] |
Thu, 26 March 2015 09:16  |
 |
suji6281
Messages: 134
Registered: September 2014
|
Senior Member |
|
|
Hello All,
Please help us to write a query to insert rows without having unique constraint error.
Requirement is insert following 8 activites to all 5 projects listed below. Provide generic query which will help us for other projects as well.
Note: Ativites are only 8 and will not change i.e we can hard-code them in query. And assume projects and activities are provided in Excel.
I will use excel formulas for insert statements for each project with given 8 activities.
Activities List:
TOWER
ELECTRICAL
WOODWORK
PAINTING
FLOORING
PLUMBING
KITCHEN
MAINHALL
Projects:
123456
123457
123458
123459
123450
Create a table.
SQL> CREATE TABLE project_tbl (project_id VARCHAR2(11) NOT NULL, activity_id VARCHAR2(10) NOT NULL);
Table created
create a PRIMARY KEY constraint on "project_id" and "activity_id" columns.
SQL > ALTER TABLE project_tbl ADD PRIMARY KEY (project_id, activity_id);
Insert rows into table.
Insert into project_tbl values ('123456', 'TOWER');
Insert into project_tbl values ('123457', 'ELECTRICAL');
Insert into project_tbl values ('123458', 'WOODWORK');
Insert into project_tbl values ('123459', 'PAINTING');
SQL> Insert into project_tbl values ('123456', 'TOWER');
1 row created.
SQL> Insert into project_tbl values ('123457', 'ELECTRICAL');
1 row created.
SQL> Insert into project_tbl values ('123458', 'WOODWORK');
1 row created.
SQL> Insert into project_tbl values ('123459', 'PAINTING');
1 row created.
Output would be like, if take project 123456, it should have all rows along with the one we inserted in above insert statement.
Thank You!
|
|
|
|
|
|
|
|
| Re: insert statement w/o having unique constraint error [message #635322 is a reply to message #635321] |
Thu, 26 March 2015 12:16  |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:we will have only 5 insert statements since 8 activities are common for all 5 projects.
This makes no sense for me.
insert into project_tbl (project_id, activity_id)
select p.project_id, a.activity_id
from projects p, activities a
where not exist (select null from project_tbl pa
where pa.project_id = p.project_id and pa.activity_id = a.activity_id)
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
