CODEWITHSUNDEEP
postgresql
Bera
Bera

Reputation: 1949

Join tables insert into third

Table1

id  class_id  class
1   
2   
3

Table2 

sameid  class_id
2       20
3       30
1       40

Table3 

class_id    desc
30          forest
40          urban
20          water

I want to insert Table3.class_id and Table3.desc into Table1.class_id and Table1.class by joining Table1-Table2-Table3.

So I try:

INSERT INTO Table1 (class_id,class)
SELECT Table3.class_id, Table3.desc
FROM Table2
INNER JOIN Table3 ON Table2.class_id=Table3.class_id
WHERE Table1.id = Table2.sameid

But I get error: invalid reference FROM-clause entry for table "Table1". What am I doing wrong?

Upvotes: 0

Views: 51

Answers (2)

Royal Wares
Royal Wares

Reputation: 1272

I'm aware an accepted answer already exists, but you can leverage views if you want to keep your "Table1" concept up to date without having to periodically insert more data

CREATE VIEW Table1_vw AS
SELECT Table3.class_id, Table3.desc
FROM Table2
INNER JOIN Table3 ON Table2.class_id=Table3.class_id
WHERE Table1.id = Table2.sameid

Then you can query Table1_vw as if it was a real table

SELECT * FROM Table1_vw;

Edit: Read through the comments to see how inserts and updates work in the context of views.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

We can do an update join with the help of the following CTE:

WITH cte AS (
    SELECT t2.sameid, t2.class_id, t3.desc
    FROM table2 t2
    INNER JOIN table3 t3
        ON t2.class_id = t3.class_id
)

UPDATE table1 AS t1
SET
    class_id = t2.class_id,
    class    = t2.desc
FROM cte AS t2
WHERE t1.id = t2.sameid;

The strategy here is to create a CTE which contains all the columns we want to use in the update, addressable by sameid, which maps to the id in table1. Then, we just use a Postgres update join to bring in the values.

Upvotes: 2

Related Questions