使用左连接的 MySQL 更新查询

发布于 2024-09-09 11:23:15 字数 727 浏览 5 评论 0原文

表架构

表名称：file_manager_folder

行：id 、 parentId、name

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

如果尚不存在具有相同parentId和名称的文件夹，我希望我的更新仅“移动”文件夹。这是您在任何普通文件系统下所期望的行为。

例如：

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

将是一个不检查有关parentId和名称的任何内容的查询...但是我怎样才能让左连接工作。

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

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 条件 WHERE 条件

原文

Table Schema

Table Name: file_manager_folder

Rows: id , parentId, name

My query simulates moving a folder into another folder and accepts an array using 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.

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.

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

喵星人汪星人 2024-09-16 11:23:15

因此，仅当目标父文件夹下不存在同名文件夹时，您才需要移动文件夹：

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）。

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

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

回复收藏 0 原文

烟凡古楼 2024-09-16 11:23:15

我认为这应该使用唯一约束/索引来解决a> 在 parentid 和 name 列上。否则，任何对表具有 INSERT/UPDATE 访问权限的人都可以规避您的业务规则。

CREATE UNIQUE INDEX blah_uk ON FILE_MANAGER_FOLDER(parentId, name) USING BTREE

I think this should be solved using a unique constraint/index on the parentid and name columns. Otherwise, anyone with INSERT/UPDATE access to the table can circumvent your business rule.

CREATE UNIQUE INDEX blah_uk ON FILE_MANAGER_FOLDER(parentId, name) USING BTREE

回复收藏 0 原文

黎夕旧梦 2024-09-16 11:23:15

有点天真，但是这个怎么样？

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

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

回复收藏 0 原文