CODEWITHSUNDEEP
mysqlsqldatabasepostgresql
Zumo de Vidrio
Zumo de Vidrio

Reputation: 2091

Translate MySQL query to PostgreSQL

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.

Upvotes: 0

Views: 181

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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;

Upvotes: 1

Related Questions