PostgreSQL Database - Inner Join Query Error

Question

With the help of this community, I was able to produce a query using an inner join which I thought would work for what I'm trying to do. Unfortunately, when I attempted to run the query found below, I received the following error:

ERROR: table name "bue" specified more than once

From what I've read on Google, some people have said that the "FROM bue" is not needed, but when I removed this, I got another error found below:

ERROR: syntax error at or near "INNER" at character 98

I'd very much appreciate your assistance in troubleshooting this. Thank you so very much.

Query:

UPDATE
  bue
SET
  rgn_no = chapterassociation.rgn_no,
  chp_cd = chapterassociation.chp_cd
FROM
  bue
INNER JOIN
  chapterassociation
    ON  bue.work_state = chapterassociation.work_state
    AND bue.bgu_cd = chapterassociation.bgu_cd
WHERE
  bue.mbr_no IS NULL AND bue.chp_cd IS NULL

What are you trying to accomplish? You seem to start out with UPDATE, but then jump to things used when you are trying to return data from a query. — Barak
– Barak, Commented Apr 30, 2012 at 0:47

Edmund · Accepted Answer · 2012-04-30 00:49:20Z

1

In PostgreSQL, specifying the table to be updated needs to be done only in the UPDATE clause, e.g. UPDATE bue. The FROM clause is only for additional tables referenced in the query. (If you were doing a self-join on bue, you would mention it again in the FROM clause, but you aren't in this case.)

The second error you get is likely just a simple syntax error. The other tricky thing is that JOIN/ON syntax doesn't fit in the FROM clause, so you have to move the join conditions to the WHERE clause. Try something like:

UPDATE
  bue
SET
  rgn_no = chapterassociation.rgn_no,
  chp_cd = chapterassociation.chp_cd
FROM
  chapterassociation
WHERE
  bue.mbr_no IS NULL AND bue.chp_cd IS NULL
  AND bue.work_state = chapterassociation.work_state
  AND bue.bgu_cd = chapterassociation.bgu_cd

See http://www.postgresql.org/docs/current/interactive/sql-update.html.

(N.B. At least I don't know how to put JOIN/ON into an UPDATE statement... I could be missing something.)

answered Apr 30, 2012 at 0:49

Edmund

10.8k3 gold badges42 silver badges59 bronze badges

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

2 Comments

user330315 Over a year ago

You are not missing anything. You cannot use JOIN or INNER JOIN in an UPDATE statement.

Erwin Brandstetter Over a year ago

To be precise: you can't use JOIN to link the (one!) table in the UPDATE clause to (one or more) table(s) in the FROM clause. You can JOIN tables in the FROM clause normally, though.

Collectives™ on Stack Overflow

PostgreSQL Database - Inner Join Query Error

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related