Translate MySQL query to PostgreSQL

Question

Having a problem with the plugin DMSF in redmine I have found a link with a SQL query that could fix the issue, however I am using PostgreSQL and that query returns a syntax error directly on f.project_id.

The MySQL query is:

update dmsf_files f 
set f.project_id = (select d.project_id from dmsf_folders d where d.id = f.dmsf_folder_id and d.system = 1) 
where (select dmsf_folders.system from dmsf_folders where dmsf_folders.id = f.dmsf_folder_id) = 1;

What should be the PostgreSQL equivalent ?

I have found some online Database syntax translator but unfortunately with no success at all.

what syntax error do you get?

Raymond Nijland
– Raymond Nijland

2022-01-12 10:10:50 +00:00
Commented Jan 12, 2022 at 10:10 — Raymond Nijland
– Raymond Nijland, Commented Jan 12, 2022 at 10:10

Tim Biegeleisen · Accepted Answer · 2022-01-12 10:06:32Z

1

Remove the alias from the left side of the SET clause:

update dmsf_files f 
set project_id = (select d.project_id from dmsf_folders d
                  where d.id = f.dmsf_folder_id and d.system = 1) 
where (select dmsf_folders.system from dmsf_folders
       where dmsf_folders.id = f.dmsf_folder_id) = 1;

answered Jan 12, 2022 at 10:06

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

Translate MySQL query to PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related