Insert com select - oracle error ORA 00904

Question

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

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.