MySQL Update Query using a left join

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

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

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

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

If you use an NOT IN instead of LEFT join that degrade your performance.

Run Explain before you query and the problem is obvious.