SQL error 1175 with JOIN

Question

I tried to run the following command:

UPDATE project JOIN folder ON folder.project_id = project.id
  SET project.source_folder_id = folder.id
  WHERE folder.name = 'source';

And of course I got the famous error 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Of course, I fixed that and wrote:

 … WHERE id > 0 AND folder.name = 'source';

which—of course—fails because id is ambiguous. So I wrote

  … project.id > 0 AND folder.name = 'source';

and I still get that error. I tried each of the following variants, all without success:

  … WHERE folder.id > 0 AND folder.name = 'source';
  … WHERE project.id > 0 AND folder.id > 0 AND folder.name = 'source';
  … WHERE (project.id > 0 AND folder.id > 0 AND folder.name = 'source');
  … WHERE (project.id, folder.id) > (0,0) AND folder.name = 'source';

I double-checked that on both tables, the id column actually is the primary key, which is the case. I know that I can just SET SQL_SAFE_UPDATES=0;, but this feels more like a hack and probably will be rejected by the team, and I want to understand why the query is not working? Can it be fixed to run under safe mode, and if so, how?

version:                 5.7.19-log
innodb_version:          5.7.19
version_comment:         MySQL Community Server (GPL)
version_compile_machine: x86_64
version_compile_os:      Win64

score 0 · Answer 1 · answered Jul 30 '18 at 10:28

0

Try this

UPDATE project p 
SET p.source_folder_id = (Select f.id From folder f 
                          where f.project_id = p.id and f.name = 'source');

answered Jul 30 '18 at 10:28

Gaj

888
5
5

Not working, too. But I am up to the point that maybe my database is spoiled. – Matthias Ronge Jul 30 '18 at 11:08
The same. [Here is a Gist of the DB](https://gist.github.com/matthias-ronge/bacada62c95e5ac21877d1f55f6f7efc) if you want to play around, the comand is below the last lines. – Matthias Ronge Jul 30 '18 at 11:25
that dump dont have source folder id column in project and name column in folder – Gaj Jul 30 '18 at 11:40
no, the naming is slightly different. See below the dump, the last lines, it is the syntax like in the question. – Matthias Ronge Jul 31 '18 at 13:59

SQL error 1175 with JOIN

1 Answers1