使用左连接的 MySQL 更新查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3248531/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Update Query using a left join
提问by Layke
Table Schema
表模式
Table Name: file_manager_folder
表名： file_manager_folder
Rows: id, parentId, name
行：id, parentId,name
My query simulates moving a folder into another folder and accepts an array using IN(?).
我的查询模拟将文件夹移动到另一个文件夹并使用 IN(?) 接受数组。
I want my update to only 'move' a folder if there is not already a folder with the same parentId and name. The kind of behaviour you would expect under any normal file system.
如果还没有具有相同 parentId 和名称的文件夹，我希望我的更新仅“移动”一个文件夹。您在任何普通文件系统下所期望的行为类型。
So for example:
例如：
UPDATE file_manager_folder set parentId = 54 where id IN( '1','2',3')
Would be a query which doesn't check anything about the parentId and name... But how can I get the left join to work.
将是一个不检查任何关于 parentId 和 name 的查询......但是我怎样才能让左连接工作。
Here is one I tried.. which totally doesn't work.
这是我试过的一个……完全不起作用。
SELECT * FROM
file_manager_folders as a
LEFT JOIN file_manager_folders as b on a.id = b.id
WHERE b.id IS NOT NULL and a.id IN("1","2","3") and a.parentId = 54
UPDATE table1 LEFT JOIN table2 SET t1.x = t2.y ON condition WHERE conditions
UPDATE table1 LEFT JOIN table2 SET t1.x = t2.y ON condition WHERE conditions
回答by Bill Karwin
So you want to move folders only if a folder of the same name under the target parent folder does notexist:
所以，你要只有当目标父文件夹下的同名文件夹不移动文件夹不存在：
UPDATE file_manager_folder f1
LEFT OUTER JOIN file_manager_folder f2
ON f1.name = f2.name AND f2.parentId = 54
SET f1.parentId = 54
WHERE f2.name IS NULL AND f1.id IN (1,2,3);
The join condition searches for a folder with the same name under the target parent. The WHERE clause tests that no such folder exists (f2.name is null only if the outer join finds no match).
加入条件搜索目标父目录下的同名文件夹。WHERE 子句测试不存在这样的文件夹（f2.name 仅在外部联接未找到匹配项时才为空）。
回答by eyescream
Kind of naive but how about this?
有点天真，但这又如何呢？
UPDATE file_manager_folder SET parentId = 54
WHERE id IN( '1','2','3')
AND parentId != 54
AND name NOT IN (SELECT name FROM file_manager_folder WHERE id IN ('1', '2', '3'))
回答by OMG Ponies
I think this should be solved using a unique constraint/indexon the parentidand namecolumns. Otherwise, anyone with INSERT/UPDATE access to the table can circumvent your business rule.
我认为这应该使用和列上的唯一约束/索引来解决。否则，任何对表具有 INSERT/UPDATE 访问权限的人都可以绕过您的业务规则。parentidname
CREATE UNIQUE INDEX blah_uk ON FILE_MANAGER_FOLDER(parentId, name) USING BTREE
回答by Sunil
If you use an NOT INinstead of LEFT jointhat degrade your performance.
如果您使用NOT IN而不是LEFT join那会降低您的性能。
Run Explain before you query and the problem is obvious.
在查询之前运行 Explain，问题就很明显了。
