MySQL Update Query using a left join

Question

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

Bill Karwin · Accepted Answer · 2010-07-14 17:07:29Z

67

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

answered Jul 14, 2010 at 17:07

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Layke Over a year ago

Thats perfect Bill. I can see I had the basics in place.. just incredibly off the mark! I don't know how you nailed it so quickly. I couldn't figure out what I wanted to join on.

OMG Ponies · Accepted Answer · 2010-07-14 17:11:56Z

2

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

answered Jul 14, 2010 at 17:11

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Comments

Savas Vedova · Accepted Answer · 2015-03-10 21:12:51Z

2

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

edited Mar 10, 2015 at 21:12

Savas Vedova

5,7023 gold badges30 silver badges45 bronze badges

answered Jul 14, 2010 at 17:09

eyescream

19.8k3 gold badges39 silver badges55 bronze badges

Comments

Rahul Sharma · Accepted Answer · 2017-01-13 07:00:10Z

0

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

Run Explain before you query and the problem is obvious.

edited Jan 13, 2017 at 7:00

Rahul Sharma

2,8932 gold badges29 silver badges41 bronze badges

answered Jan 13, 2017 at 5:43

Sunil

1

Collectives™ on Stack Overflow

MySQL Update Query using a left join

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related