使用左联接的MySQL更新查询 [英] MySQL Update Query using a left join

问题描述

表架构

表名称:file_manager_folder

行:id，parentId，name

我的查询模拟将一个文件夹移动到另一个文件夹，并使用IN(?)接受一个数组.

My query simulates moving a folder into another folder and accepts an array using IN(?).

如果不存在具有相同parentId和名称的文件夹，我希望我的更新仅移动"一个文件夹.您在任何普通文件系统下所期望的行为.

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.

例如:

UPDATE file_manager_folder set parentId = 54 where id IN( '1','2',3') 

将是一个查询，不检查任何有关parentId和名称的信息...但是如何使左联接起作用.

Would be a query which doesn't check anything about the parentId and name... But how can I get the left join to work.

这是我尝试过的一个..这完全不起作用.

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

推荐答案

因此，仅当目标父文件夹下的同名文件夹不存在时，才想移动文件夹:

So you want to move folders only if a folder of the same name under the target parent folder does not exist:

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);

联接条件在目标父目录下搜索具有相同名称的文件夹. WHERE子句测试不存在这样的文件夹(仅当外部联接找不到匹配项时，f2.name才为null).

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).

这篇关于使用左联接的MySQL更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！