Postgresql update a table with join

Question

I need to update a Table using information from other table. This is my sql query:

UPDATE service a 
JOIN agency b USING (feed_id) 
SET end_date = (SELECT MIN(start_date)-1 
                FROM service c 
                JOIN agency d USING (feed_id) 
                WHERE b.feed_id = a.feed_+1 
                      AND b.agency_id = d.agency_id)

I need agency id in the condition so the only way is join both table with agency table.

Postgres return:

ERROR:  syntax error at or near "JOIN"
LINE 1: UPDATE service a JOIN agency b USING (feed_id) SET end_date ...

Any tips?

"doesn't work" is not a valid Postgres error message. Where in the manual did you find that syntax? — user330315
– user330315, Commented Oct 27, 2016 at 14:24
Please edit your question add the create table statements for the tables in question, some sample data and the expected result based on that data. Formatted text please, no screen shots — user330315
– user330315, Commented Oct 27, 2016 at 14:41

Grisha Ghukasyan · Accepted Answer · 2016-10-27 16:09:05Z

1

Try this :)

UPDATE service
SET end_date = (SELECT MIN(start_date)-1 
                FROM service c 
                JOIN agency d USING (feed_id) 
                WHERE b.feed_id = a.feed_+1 
                      AND b.agency_id = d.agency_id)
FROM service a
JOIN agency b USING (feed_id)

answered Oct 27, 2016 at 16:09

Grisha Ghukasyan

1001 silver badge8 bronze badges

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

3 Comments

user330315 Over a year ago

Do not repeat the target table in the FROMclause of an UPDATE statement.

Grisha Ghukasyan Over a year ago

i take syntax error at or near "JOIN" if i doesn't

franco_b Over a year ago

Doesn't work. It's better add second join in the subquery

franco_b · Accepted Answer · 2016-11-14 12:49:56Z

0

This query works:

UPDATE service a
SET end_date = (SELECT MIN(start_date)-1
            FROM service b
            JOIN agency c ON b.feed_id = c.feed_id
            JOIN agency d ON a.feed_id = d.feed_id 
            WHERE b.feed_id = a.feed_id+1 
            AND c.agency_id = d.agency_id
            )

answered Nov 14, 2016 at 12:49

franco_b

9286 gold badges15 silver badges34 bronze badges

Collectives™ on Stack Overflow

Postgresql update a table with join

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related