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?

Parfait · Accepted Answer · 2020-07-09 16:24:54Z

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 9, 2020 at 16:24

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

3 Comments

the man Over a year ago

I keep getting the error: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) missing FROM-clause entry for table "table2"?

Parfait Over a year ago

Is there a table named table2 in database? Error is titled UndefinedTable.

the man Over a year ago

I forgot to put the schema in front of the table name. Thanks for the help

Jim Macaulay · Accepted Answer · 2020-07-09 16:20:25Z

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 9, 2020 at 16:20

Jim Macaulay

5,2515 gold badges32 silver badges60 bronze badges

Comments

Mike Organek · Accepted Answer · 2020-07-09 16:21:44Z

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 9, 2020 at 16:21

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Comments

Rémy Baron · Accepted Answer · 2020-07-09 16:23:42Z

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 9, 2020 at 16:23

Rémy Baron

1,4099 silver badges15 bronze badges

2 Comments

Parfait Over a year ago

Be careful here. Postgres does not allow target columns in SET to be aliased. A frustrating and weird error for newcomers.

Rémy Baron Over a year ago

effectively : copy/paste error of the original resuest

Collectives™ on Stack Overflow

updating PosgreSQL database via SQLalchemy; syntax error

4 Answers 4

3 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related