How to update Column in sql with some condition

Question

4.00/5 (2 votes)

See more:

In My Table I have a table Let's say Student
and this student table has columns Hobies and subject class
Now what i need to do is

SQL

if(Student.class=2 and Student.Hobies like '%basketball%')
Update Student Set Student.Subject='BasketBall'
if(Student.class=2 and Student.Hobies like '%Football%')
Update Student Set Student.Subject='Football'

But it throw an error

SQL

The multi-part identifier "dbo.Student.class" could not be bound.

SQL

The multi-part identifier "dbo.Student.Hobies" could not be bound.

Does anyone have Idea why it is happening
-Thanks

Posted 13-Oct-14 21:22pm

Vishal Pand3y

Updated 13-Oct-14 21:37pm

v3

Add a Solution

Comments

Herman<T>.Instance 14-Oct-14 3:29am

Hobies or Hobbies column?

Vishal Pand3y 14-Oct-14 3:31am

@digimanus does it matter it's just a typo and yes you are right it is hobies

Herman<T>.Instance 14-Oct-14 4:16am

I was wondering if the name in he source was spelled incorrect and that might have been a cause..

KaushalJB 14-Oct-14 3:31am

Are you using any kind of ALIAS for your tables?

Vishal Pand3y 14-Oct-14 3:32am

yes but all alias are same ie dbo

Rajesh waran 14-Oct-14 3:37am

try solution 1

2 solutions

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)

Arora_Ankit · Accepted Answer · 2014-10-13T21:32:00

Solution 1

It will be like this:-

SQL

Update Student Set Subject='BasketBall'
where class=2 and Hobies like '%basketball%'


If you have multiple conditions the use it like this

Update Student Set Subject=(CASE WHEN class=2 and Hobies like '%basketball%' THEN 'BasketBall'
WHEN class=2 and Hobies like '%Football%' THEN 'Football'
END)

Posted 13-Oct-14 21:32pm

Arora_Ankit

Updated 13-Oct-14 21:44pm

v2

Comments

Vishal Pand3y 14-Oct-14 3:38am

Actually I have multiple condition so i can't use where

Arora_Ankit 14-Oct-14 3:44am

Answer updated.

Rajesh waran 14-Oct-14 5:26am

But u didn't mention that u have multiple condition,arora_ankit solution is right for the question that u have provided here.

Arora_Ankit 14-Oct-14 5:39am

Yes Raj, that answer was correct and now I improved that for his need too.

MokarramNazri · Accepted Answer · 2014-10-14T22:09:00

Solution 2

SQL

Update Student Set Student.Subject=
( Case 
  When Student.class=2 and Student.Hobies like '%basketball%' Then 'BasketBall'
  When Student.class=2 and Student.Hobies like '%Football%' Then 'Football' Else       Student.Subject END)

Posted 14-Oct-14 22:09pm

MokarramNazri

Updated 15-Oct-14 1:53am

v2