Insert using time table only at values that do not exist in the main table

Asked

Viewed 40 times

0

I am trying to make an Insert in a table using another temporary table, but the Insert can only occur if the item does not exist in the original table.

create temp table tmp_x as select * from solicitacao_materiais limit 0;

my Insert is like this. 

insert into solicitacao_materiais(codigo_material,quantidade_estoque,descricao_material,almoxarifado_relacionamento_id)
select codigo_material,quantidade_estoque,descricao_material,almoxarifado_relacionamento_id
from tmp_x
where solicitacao_materiais.codigo_material != tmp_x.codigo_material;

I’m using the material code to make the comparison because each item has a unique cogido and beyond the temporary table do not give the id’s to the items
but I’m getting the following error.

ERROR: invalid Reference to FROM-clause entry for table "requecao_materials" LINE 4: Where request_materials.codigo_material != tmp_x.codigo_... HINT: There is an entry for table "solicitacao_materiais", but it cannot be referenced from this part of the query.

Someone would tell me what I’m doing wrong?

postgresql

  • Have you ever tried to place a subconsultate in your Where? Where not(tmp_x.codigo_material in (select codigo_material from solicitacao_materials))

  • 1

    It worked out thank you :D

1 answer

1


Correction of your query, use where EXISTS:

insert into solicitacao_materiais(codigo_material,quantidade_estoque,descricao_material,almoxarifado_relacionamento_id)
select codigo_material,quantidade_estoque,descricao_material,almoxarifado_relacionamento_id
from tmp_x
where EXISTS( SELECT codigo_material FROM solicitacao_materiais WHERE codigo_material != tempx.codigo_material );

minimal example: http://sqlfiddle.com/#! 17/bedf34/1

Browser other questions tagged postgresql

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