Insert com select - oracle error ORA 00904

Asked

Viewed 126 times

1

I’m having trouble understanding why this mistake. I want to insert a field {descricao_posto} on the table TB_POSTOS_NO_CONTRATO, using a select from another: TB_POSTO. A 1a. table has a fk_post that refers to the id of the second table, and yet it does not recognize it. Any idea?

INSERT INTO TB_POSTOS_NO_CONTRATO (DESCRICAO_POSTO) 

SELECT p.DESCRICAO FROM TB_POSTO 

where  TB_POSTOS_NO_CONTRATO.fk_posto = tb_posto.id

Error message:

ORA-00904: "TB_POSTOS_NO_CONTRATO"." FK_POSTO": invalid Identifier

  • put the structure of the 2 tables

  • TB_POSTOS_NO_CONTRATO "ID" NUMBER NOT NULL ENABLE, "FK_POSTO" NUMBER NOT NULL ENABLE, "SALARIO_BASE" NUMBER(7,2), "DESCRICAO_POSTO" VARCHAR2(60), CONSTRAINT "TB_POSTOS_NO_CONTRATO_PK" PRIMARY KEY ("ID") USING INDEX ENABLE ALTER TABLE "TB_POSTOS_NO_CONTRATO" ADD CONSTRAINT "TB_POSTOS_NO_CONTRATO_FK" FOREIGN KEY ("FK_POSTO") REFERENCES "TB_POSTO" ("ID") ENABLE

  • "TB_POSTO" ( "ID" NUMBER NOT NULL ENABLE, "DESCRICAO" VARCHAR2(150) NOT NULL ENABLE, CONSTRAINT "TB_POSTO_PK" PRIMARY KEY ("ID") USING INDEX ENABLE,

1 answer

1

There are some problems there, the ID or FK_POSTO are being referenced in the Insert and since they are not null fields, they must exist. To start would create a quence to fill the id of table TB_POSTOS_NO_CONTRATO, as described below:

CREATE SEQUENCE SEQ_TB_POSTOS_NO_CONTRATO START WITH 1;

And then change the sql leaving it like this:

INSERT INTO TB_POSTOS_NO_CONTRATO (ID,FK_POSTO,DESCRICAO_POSTO) 

SELECT SEQ_TB_POSTOS_NO_CONTRATO.NEXTVAL,
       P.ID,
       P.DESCRICAO 
 FROM TB_POSTO P, TB_POSTOS_NO_CONTRATO PC

WHERE PC.FK_POSTO = P.ID;

But this sql would include a possible insertion of the TB_POSTO ID that already existed in the table TB_POSTOS_NO_CONTRATO.

For example, if there was a row below in table TB_POSTOS_NO_CONTRATO:

ID | FK_POSTO | DESCRICAO_POSTO
1  |    1     | null

With this sql, a new line like the following would be created:

ID | FK_POSTO | DESCRICAO_POSTO
2  |    1     | "Descrição do posto"

Getting the table with the two lines:

ID | FK_POSTO | DESCRICAO_POSTO
1  |    1     | null
2  |    1     | "Descrição do posto"

To include the description in a row of a TB_POSTO ID that already existed in the TB_POSTOS_NO_CONTRATO table, you have to do an update and not an Insert, as we see below:

UPDATE TB_POSTOS_NO_CONTRATO 
   SET DESCRICAO_POSTO=(SELECT DESCRICAO FROM TB_POSTO WHERE TB_POSTOS_NO_CONTRATO.FK_POSTO =TB_POSTO.ID);

Using this sql would be updating the existing row, only including the description that would exist in the table TB_POSTO.

  • Thank you Bruno Cunha. For my part, there was a strong misunderstanding. The records already existed so just the update. For Insert it would be necessary to put the NOT NULL fields even. The Quence already exists and with Trigger . Solved, it was worth!!!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.