Insert values from table into two separate tables (Postgresql)

Question

I am trying to populate a table by inserting the values from a temp table as well as a single value from another table.

I have the SQL down to insert values from my temp table into my real table except for one column, and that column is a foreign key to another table. Let me give some example sql.

INSERT INTO location(value1,value2,value3,foreign_key_value)
SELECT tmpValue1
       ,tmpValue2
       ,tmpValue3
       ,(foreign_key_value from a different table needs to be here)
FROM tmp_location

If the foreign_key_value was found in my tmp table I could do a join, but that is not where I need to get the information from. I was wondering if there is anyway to reference the column value from my table where the foreign key points to in my Select statement. Or if there is a better way to do this.

If you need more information or clarification let me know!

Clodoaldo Neto · Accepted Answer · 2013-04-01 16:18:39Z

2

insert into location(value1,value2,value3,foreign_key_value)
select tmpvalue1
       ,tmpvalue2
       ,tmpvalue3
       ,(select id from merchant where the_value = l.the_value)
from
    tmp_location l

edited Apr 1, 2013 at 16:18

answered Apr 1, 2013 at 15:52

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

parchambeau Over a year ago

Well the problem is that my tmp_location table does not have the value that I need in it. So I can't join based on that key. It is 3 seperate tables.

parchambeau Over a year ago

location table (Final Product) tmp_location (has all values needed except 1) merchant (contains the merchant_id which is needed on every row in "location table")

Clodoaldo Neto Over a year ago

@Loeras You need to show how the are tables related. Show the output of => \d location => \d tmp_location and => \d merchant

parchambeau Over a year ago

Sorry I'm not sure how to use \d but the only relation in this whole thing is between location and merchant, they are related on merchant_id. merchant_id is the primary key of merchant and a foreign key of location. tmp_location is just a table I am populating with data from a CSV and then using some of the values from it to populate location.

parchambeau Over a year ago

So basically, the location table needs to grab the value of the primary key in merchant to populate location.merchant_id, and it needs to happen within the same query because I am getting a "null value in merchant_id violates not null constraint" error without it. Thank you for your help!

|

Collectives™ on Stack Overflow

Insert values from table into two separate tables (Postgresql)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related