Translate MySQL query to PostgreSQL

January 12, 2022

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.

>Solution :

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;