Updating a column name to be a concatenation based on the same table

Question

Need Help in SQL SERVER! I have two tables: Students: Student_Id, Student_Name Grades: Student_Id, Profession, Grade. I need to change the column Profession to have the value Profession + Grade only for those students who have more than one profession. Is it possible? I tried:

UPDATE Grades SET profession = CONCAT(profession," + ",grade) WHERE student_id IN (SELECT student_id FROM Grades GROUP BY student_id HAVING COUNT(profession)>1);

Results Query Error: Error: ER_UPDATE_TABLE_USED: You can't specify target table 'Grades' for update in FROM clause

Answer 1

I am not quite sure if I understand your data model from your description, so please check the data to be updated before running it.

You can break the update into two separate statements with a temp table or CTE, which will give you the benefit of seeing the records you want to update. (Use a select from the temp table or CTE beforehand to verify the records found.)

Example

-- Load a temp table with students to update
DROP TABLE IF EXISTS #X

SELECT 
student_id

INTO #X
FROM 
Grades
GROUP BY
student_id
HAVING
COUNT(DISTINCT profession) > 1 -- Assuming they could have multiple grades. 

UPDATE G

SET profession = CONCAT(profession," + ",grade)

SET 
FROM 
Grades G
INNER JOIN #X X
ON G.student_id = X.student_id 

WHERE
X.student_id IS NOT NULL -- Always helpful to have WHERE clause to avoid any accidents.

Answer 2

This should give you what you're looking for all in one go. Feel free to tweak how you concatenate the Profession and Grade column.

WITH CTE_StudentWithMultipeProfessions AS
(
    SELECT Student_Id
    FROM Grades
    GROUP BY Student_Id
    HAVING COUNT(1) > 1
)

UPDATE G
SET G.Profession = CONCAT(G.Profession, ' + ', G.Grade)
FROM Grades AS G
INNER JOIN CTE_StudentWithMultipeProfessions AS MP
    ON G.Student_Id = MP.Student_Id