Is it possible to bulk insert using CTE in MySQL?

Question

I created this small sample fiddle to play with the model: https://www.db-fiddle.com/f/b37ASVuSxna9wEoWn9hZDw/0

I have a table called folder, which represents a folder hierarchy with a parent_id column pointing to the parent folder. The root folder in a tree has NULL as its parent_id.

I have another table that specifies access restrictions to folders for users and user groups, it is called folder_access. It has a foreign key relation to the folder table, and then it has columns for user_id, user_group_id and a flag called access (which is an int column, but it is used as a bitwise flag that signifies access like read, write, delete (not important in relation to this question)).

I am implementing an API that gets a list of folder ids, a list of either user ids or user group ids and a single access value. The API should then insert or update rows in the folder_access table like this:

1. Insert a row for each combination of folder id and user id (or user group id)
2. Also insert a row for each descendant folder in the hierarchy
3. If a row already exists for the given combination of folder id, user id/user group id, then update the access value for that row

I implemented a version without the second requirement above:

public async Task SaveFolderAccess(long areaId, List<long> folderIds, List<long> userIds, List<int> userGroupIds, Access access)
{
 var values = new List<string>();
 foreach (var folderId in folderIds)
 {
 foreach (var userId in userIds)
 {
 values.Add($"({areaId},{folderId},{userId},NULL,{(int)access})");
 }
 foreach (var userGroupId in userGroupIds)
 {
 values.Add($"({areaId},{folderId},NULL,{userGroupId},{(int)access})");
 }
 }
 await dbSession.ExecuteSqlRawAsync(
 @$"INSERT INTO folder_access (area_id, folder_id, user_id, user_group_id, access)
 VALUES {string.Join(",", values)}
 ON DUPLICATE KEY UPDATE access = {(int)access}");
}

I know I can create a common table expression to find all the descendant folder ids for a particular folder, like this (replacing {folderId} with the actual value):

WITH RECURSIVE CTE (id) AS
(
SELECT f.id
 FROM folder AS f
 WHERE id = {folderId}
UNION ALL
SELECT f.id
 FROM CTE JOIN folder AS f
 ON f.parent_id = CTE.id
)
SELECT id FROM CTE

But is it possible to combine this into one big command that leverages both the CTE and the "ON DUPLICATE KEY UPDATE"?

Answer 1

The task is not defined completely, but nevertheless:

-- Parameters 
SET @folderId:=2;
SET @user_ids:='11,14';
SET @access_mask:=2;

-- Insert/update access masks for single users
INSERT INTO FOLDER_ACCESS (FOLDER_ID, USER_ID, ACCESS)
WITH RECURSIVE 
folder_ids AS ( SELECT f.id
 FROM FOLDER AS f
 WHERE id = @folderId
 UNION ALL
 SELECT f.id
 FROM folder_ids 
 JOIN FOLDER AS f ON f.parent_id = folder_ids.id ),
user_ids AS ( SELECT SUBSTRING_INDEX(@user_ids, ',', 1) user_id,
 CONCAT(SUBSTRING(@user_ids FROM 1 + LOCATE(',', @user_ids)), ',') slack
 UNION ALL
 SELECT SUBSTRING_INDEX(slack, ',', 1),
 SUBSTRING(slack FROM 2 + LENGTH(SUBSTRING_INDEX(slack, ',', 1))) 
 FROM user_ids
 WHERE slack )
SELECT folder_ids.id, user_ids.user_id, @access_mask
FROM folder_ids, user_ids
ON DUPLICATE KEY 
UPDATE ACCESS = @access_mask;

fiddle

The query for groups access masks update is absolutely similar.

---

If a row already exists for the given combination of folder id, user id/user group id, then update the access value for that row

"Update" is too vague term.

I think that the flag of access mask change type must exist. For example, @type, with the values:

1. unconditional replace
2. set bits
3. clear bits

And update clause will be

UPDATE ACCESS = CASE @type WHEN 1 THEN @access_mask
 WHEN 2 THEN ACCESS | @access_mask
 WHEN 3 THEN ACCESS & ~@access_mask
 ELSE ACCESS
 END