Updating a column name to be a concatenation based on the same table 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,&quot; + &quot;,grade) WHERE student_id IN (SELECT student_id FROM Grades GROUP BY student_id HAVING COUNT(profession)&gt;1);
Results
Query Error: Error: ER_UPDATE_TABLE_USED: You can't specify target table 'Grades' for update in FROM clause
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) &gt; 1 -- Assuming they could have multiple grades. 

UPDATE G

SET profession = CONCAT(profession,&quot; + &quot;,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.