How to insert statement in an inner join

Question

0.00/5 (No votes)

See more:

Hi,

I need to insert statement on table D after i have joined table D & C.

hows the procedure?

I tried,

SQL

INSERT INTO TABLE D( D.depot_id, D.city_id)VALUES( 45, 99);
SELECT C.city_id, D.city_id
FROM DEPOT D
INNER JOIN TABLE C
ON D.city_id = C.city_id

it turned up error, displaying

SQL

INSERT INTO DEPOT D( D.depot_id, D.city_id)VALUES( 45, 99)
*
Error at line 1:
ORA-02291: integrity constraint (S100636395.SYS_C002144957) violated - parent key not found

ON D.city_id = C.city_id
               *
Error at line 4:
ORA-00904: "C"."CITY_ID": invalid identifier

Posted 28-Mar-15 13:26pm

Member 11563432

Updated 29-Mar-15 19:57pm

Jörgen Andersson

v2

Add a Solution

Comments

Jörgen Andersson 30-Mar-15 1:56am

In addition to what David says, I would like to add that the insert statement can't use an alias from a sub query

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David Rush · Answer 1 · 2015-03-28T15:26:00

I see three things here.

1) TABLE is a reserved keyword. While it is possible to get Oracle to accept it, it is in bad form to have a table named TABLE.

2) There is a constraint named S100636395.SYS_C002144957 which requires a field in table DEPOT to be one of a set of values defined in the constraint. It is probably a foreign key constraint, usually meaning it has to be a primary key of another table. There is not sufficient information provided on the table structure for me to elaborate further.

3)Finally, the Invalid identifier error means it can not find column CITY_ID in TABLE, which you have aliased as C (perhaps related to note #1).