user6721756 user6721756 - 3 months ago 6
MySQL Question

Update statement in nested query

I have an update query.But it showing sql error

Query

Update ms_student set student_first_name=y.student_first_name,student_middle_name=y.student_middle_name,student_last_name=y.student_last_name FROM
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(student_name, ' ', 1), ' ', -1) as student_first_name,
SUBSTRING_INDEX(SUBSTRING_INDEX(student_name, ' ', 2), ' ', -1) as student_middle_name,
SUBSTRING_INDEX(SUBSTRING_INDEX(student_name, ' ', 3), ' ', -1) as student_last_name
FROM ms_student) y


My inner query working perfectly.But In update having issue

Answer

Why you need subquery you can do with simple update command

update ms_student
set 
student_first_name= SUBSTRING_INDEX(SUBSTRING_INDEX(student_name, ' ', 1), ' ', -1),
student_middle_name = SUBSTRING_INDEX(SUBSTRING_INDEX(student_name, ' ', 2), ' ', -1),
student_last_name = SUBSTRING_INDEX(SUBSTRING_INDEX(student_name, ' ', 3), ' ', -1) ;
Comments