updating PosgreSQL database via SQLalchemy; syntax error

Question

I have two tables in a PostgreSQL database; table1, table2. They both contain an id column. I want to add a column from table2, say col1, to table1 where table1.id = table2.id.

I am attempting to dot this via SQLalchemy. I keep getting the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "INNER"

Here is a snippet of the code:

engine = create_engine(...)

with engine.connect() as con:
   con.execute("ALTER TABLE table1 ADD COLUMN col1 text")
   con.execute("UPDATE table1  \
                INNER JOIN table2 ON table1.id = table2.id \
                SET table1.col1 = table2.col1")

Why am I getting this error?

score 1 · Accepted Answer · answered Jul 09 '20 at 16:24

1

PostgreSQL does not support UPDATE...JOIN like MySQL and MS Access. However, Postgres does support UPDATE...FROM even UPDATE...FROM...JOIN:

con.execute("""UPDATE table1 t1
               SET col1 = t2.col1
               FROM table2 t2
               WHERE t1.id = t2.id""")

answered Jul 09 '20 at 16:24

Parfait

104,375
17
94
125

I keep getting the error: ```sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) missing FROM-clause entry for table "table2"```? – the man Jul 09 '20 at 16:38
Is there a table named `table2` in database? Error is titled `UndefinedTable`. – Parfait Jul 09 '20 at 16:43
I forgot to put the schema in front of the table name. Thanks for the help – the man Jul 09 '20 at 16:44

score 0 · Answer 2 · answered Jul 09 '20 at 16:20

0

You cannot have the joins directly while updating in postgres and the way you have joined is incorrect. Please use below query,

update table1 set table1.col1 = qry.col1
from
(select col1 from table2) qry
where 
table1.id = table2.qry;

answered Jul 09 '20 at 16:20

Jim Macaulay

4,709
4
28
53

score 0 · Answer 3 · answered Jul 09 '20 at 16:21

0

That is not the correct syntax for update. Please see https://www.postgresql.org/docs/current/sql-update.html

update table1
   set col1 = table2.col1
  from table2
 where table2.id = table1.id;

answered Jul 09 '20 at 16:21

Mike Organek

11,647
3
11
26

score 0 · Answer 4 · answered Jul 09 '20 at 16:23

0

correct syntax for update with another table in postgresql :

 UPDATE table1 
 set table1.col1 = table2.col1
 FROM table2 
 where  table1.id = table2.id

answered Jul 09 '20 at 16:23

Rémy Baron

1,379
8
15

Be careful here. Postgres does not allow [target columns in `SET` to be aliased](https://stackoverflow.com/a/11370132/1422451). A frustrating and weird error for newcomers. – Parfait Jul 09 '20 at 16:28
effectively : copy/paste error of the original resuest – Rémy Baron Jul 09 '20 at 22:27

updating PosgreSQL database via SQLalchemy; syntax error

4 Answers4