Click here to Skip to main content
15,883,705 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I Have a table for Student which has field hobbiesId which I want to Update
and I have another table for Hobbies which has field name and id

what i want to do is
match description of table student with name of table hobbies if it matches then update HobbiesId by Id of table Hobbies this is what I have done
Update Student Set HobbiesId=(CASE 
                                 WHEN Description like (Select Name From [Hobbies])                            THEN-- Id of Hobbies

Also in like it may returns more than column but I Need one column which is matching most
Updated 16-Nov-14 23:13pm

Try this

Update Stu Set HobbiesId=b.HobbieID from Student Stu Join [Hobbies] b
On charindex(b.Name,Stu.Description)>0
Share this answer
Vishal Pand3y 17-Nov-14 6:56am    
the problem is charindex(b.Name,Stu.Description)>0 returns more than one column so I can't update with it as it throws The multi-part identifier could not be bound error
Shweta N Mishra 17-Nov-14 7:13am    
can you write your query, charindex only return a numeric value and can not have multiple column.
Vishal Pand3y 17-Nov-14 7:25am    
I got the solution Thanks charindex helped :)
Try This............. Defiantly you will get your Solution..

Update student set student.HobbiesId=Hobbies.HobbiesId From student inner join Hobbies  on student.HobbiesId=Hobbies.HobbiesId

Note: If this Solve your Problem Please click on Accept Answer.

Share this answer
Divyam Sharma 18-Nov-14 5:32am    
Good one...
you Question not Clear, i hope your looking for this

Syntax :
Update a set From table1 as inner join table2 as b on
Share this answer
Vishal Pand3y 17-Nov-14 6:41am    
will inner join work on nvarchar and when these aren't exact match

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900