MySQL Update Query using a left join

mysql left-join
Layke picture Layke · Jul 14, 2010 · Viewed 77.5k times · Source

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

Answer

Bill Karwin picture Bill Karwin · Jul 14, 2010

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

Related questions

Insert using LEFT JOIN and INNER JOIN

Hey all i am trying to figure out how to go about inserting a new record using the following query: SELECT user.id, user.name, user.username, user.email, IF(user.opted_in = 0, 'NO', 'YES') AS optedIn FROM user LEFT …

Read More
LEFT JOIN only first row

I read many threads about getting only the first row of a left join, but, for some reason, this does not work for me. Here is my structure (simplified of course) Feeds id | title | content ---------------------- 1 | Feed 1 | ... Artists artist_id | …

Read More
How to use mysql JOIN without ON condition?

Is it possible to write join query without ON statement? and how do these joins differ LEFT JOIN, RIGHT JOIN works.

Read More