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
SQL
Update Student Set HobbiesId=(CASE 
                                 WHEN Description like (Select Name From [Hobbies])                            THEN-- Id of Hobbies
END)

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

Try this

SQL
Update Stu Set HobbiesId=b.HobbieID from Student Stu Join [Hobbies] b
On charindex(b.Name,Stu.Description)>0
 
Share this answer
 
Comments
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..

SQL
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.



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

Syntax :
SQL
Update a set a.id=b.id From table1 as inner join table2 as b on a.description=b.name
 
Share this answer
 
v2
Comments
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