PSQL join - Column "table.column" does not exist

Question

I need to update records in a table but only if a certain column of this table equals the content of a column of another table.

But first, just to be sure, I want to select all of the records that meets this specific so I've tried something like:

SELECT * FROM "table1" as ARR INNER JOIN "table2" as AOP ON ("AOP.col2" = "ARR.col1");

But the query returns this error:

ERROR: column "AOP.col2" does not exist
LINE 1: ...as ARR INNER JOIN "table2" as AOP ON ("AOP.col...

What am I doing wrong? (obviously the column does exist)
Seeing this error do you think the following update query will also fail?

UPDATE "table2" AS AOP
SET "col2" = "ARR.col1"
FROM "table1" AS ARR
WHERE "AOP.col3" = "ARR.col4";

Remove all those useless double quotes – Mar 23 '18 at 09:32 — , Mar 23 '18 at 09:32

score 4 · Accepted Answer · answered Mar 23 '18 at 10:01

4

[SOLVED]

If anyone's interested in the solution this is it:

SELECT * FROM "table1" as "ARR" INNER JOIN "table2" as "AOP" ON ("AOP"."col2" = "ARR"."col1");

answered Mar 23 '18 at 10:01

Leon

665
4
10
32

I would highly recommend to remove all those useless double quotes and never use quoted identifiers. That will give you a lot less headaches in the long run. – Mar 23 '18 at 10:03

Dwith · Answer 2 · 2018-03-23T09:46:58.063

0

SELECT * FROM table1 ARR INNER JOIN table2 AOP ON AOP.col2=ARR.col1

edited Mar 23 '18 at 09:46

answered Mar 23 '18 at 09:37

Dwith

3
6

PSQL join - Column "table.column" does not exist

2 Answers2