updating column containing list separated by | using sql
I have a table named hr_Folders with following info:
FolderId FolderName EmployeeId NumberOfEntries IsDeleted 1 Folder 1 9 1 0 2 Folder 2 9 1 0 3 Folder 3 9 1 0
and there is another table hr_EmployeeSavedRegistrants where folderids are saved as | separated list like this:
ID EmployeeID RegistrantID FolderID 44 9 4 1|2 45 9 5 1|3 46 9 6 2
I want to create a stored procedure where when i pass folderid and employeeid, it deletes that folder from hr_folder and then update the hr_EmployeeSavedRegistrants table's FolderId column accordingly ( remove the folderid from folderid column list and if there is one folderid in hr_EmployeeSavedRegistrants, then delete that row like row 3 in hr_EmployeeSavedRegistrants)
so far written code is:
CREATE PROC GetResumeCountInfolder @Folder int AS DECLARE @FolderIDs VARCHAR(100) SELECT @FolderIDs = FolderID from hr_EmployeeSavedRegistrants where deleted = 0 and EmployeeID= 9
Please suggest how to do it
Here is a solution given your table structure, however, I would agree with Mahmoud -- you really should normalize your tables. Create a new table called hr_EmployeeFolders with EmployeeId and FolderId as your two fields. Then you can store a 1-n number of employees and folders. Much easier to maintain this way.
However, given your current scenario, something like this should work:
create proc updatefolder @FolderId int, @EmployeeId int as begin update hr_Folders set isdeleted = 1 where folderId = @folderId and employeeId = @employeeId update hr_EmployeeSavedRegistrants set FolderId = replace(replace(folderId, @FolderId, ''),'||','|') where employeeId = @employeeId update hr_EmployeeSavedRegistrants set FolderId = case when left(FolderId,1) = '|' then right(folderId, len(folderId) - 1) when right(FolderId,1) = '|' then left(folderId, len(folderId) - 1) else FolderId end where employeeId = @employeeId end
There are some other options splitting the list and recreating, but this is the quickest I could think of. The first update statement updates the deleted column in the folders table. The second update statement removes the folder id from the EmployeeSavedRegistrants table, along with any potential instances of double pipes. The final update statement removes any beginning or trailing pipes.