CODEWITHSUNDEEP
mysqlleft-join
Layke
Layke

Reputation: 53156

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

Upvotes: 28

Views: 91893

Answers (4)

Sunil
Sunil

Reputation: 1

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

Run Explain before you query and the problem is obvious.

Upvotes: 0

eyescream
eyescream

Reputation: 19622

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

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332581

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

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562368

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

Upvotes: 67

Related Questions