Update a columns of table based if one column matches to other columns table

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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 16-Nov-14 23:08pm

Vishal Pand3y

Updated 16-Nov-14 23:13pm

v2

Add a Solution

3 solutions

Solution 3

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

Posted 16-Nov-14 23:33pm

aarif moh shaikh

Comments

Divyam Sharma 18-Nov-14 5:32am

Good one...

Solution 1

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

Posted 16-Nov-14 23:14pm

King Fisher

Updated 16-Nov-14 23:15pm

v2

Comments

Vishal Pand3y 17-Nov-14 6:41am

will inner join work on nvarchar and when these aren't exact match

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Shweta N Mishra · Accepted Answer · 2014-11-16T23:23:00

Solution 2

Try this

SQL

Update Stu Set HobbiesId=b.HobbieID from Student Stu Join [Hobbies] b
On charindex(b.Name,Stu.Description)>0

Posted 16-Nov-14 23:23pm

Shweta N Mishra

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 :)