Update different values in a single query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update different values in a single query

Author

Topic

crazycoder
Starting Member

3 Posts

Posted - 2011-11-20 : 03:05:56

Hi all,

I'm a beginner in sql programming and I need to write a query to update different values for same column in a table using a single query. My requirement is very much similar to the one explained below :

Table Person contains
ID, Name, Category.
1, abc, G
2, xyz, N
3, pqr, N
4, efg, G

I need to write a single update query to update all G to N and all N to G in the Category field. Please help me.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 03:37:28

its a straight forward update you want to do. Have a look at syntax of UPDATE statement in books online and try it yourself. In case you face any issues post the query and we will help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crazycoder
Starting Member

3 Posts

Posted - 2011-11-20 : 03:58:42

Well, I can update any one of it using

Update Person
Set Category='G'
Where Category='N'

But I need to update all G to N also in the same query so that the the table should have the updated data as

1, abc, N
2, xyz, G
3, pqr, G
4, efg, N

I cant find any solution to do this.

quote:
Originally posted by visakh16

its a straight forward update you want to do. Have a look at syntax of UPDATE statement in books online and try it yourself. In case you face any issues post the query and we will help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 04:11:00

[code]
Update Person
Set Category= REPLACE('NG',Category,'')
WHERE Category IN ('N','G')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crazycoder
Starting Member

3 Posts

Posted - 2011-11-20 : 04:33:10

Thanks a lot. That worked. :-)

quote:
Originally posted by visakh16

Update Person
Set Category= REPLACE('NG',Category,'')
WHERE Category IN ('N','G')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 06:11:24

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-21 : 03:23:13

quote:
Originally posted by crazycoder

Well, I can update any one of it using

Update Person
Set Category='G'
Where Category='N'

But I need to update all G to N also in the same query so that the the table should have the updated data as

1, abc, N
2, xyz, G
3, pqr, G
4, efg, N

I cant find any solution to do this.

quote:
Originally posted by visakh16

its a straight forward update you want to do. Have a look at syntax of UPDATE statement in books online and try it yourself. In case you face any issues post the query and we will help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

You could have tried

Update Person
Set Category=case when category='G' then 'N' else 'G' end

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 03:28:46

quote:
Originally posted by madhivanan

quote:
Originally posted by crazycoder

Well, I can update any one of it using

Update Person
Set Category='G'
Where Category='N'

But I need to update all G to N also in the same query so that the the table should have the updated data as

1, abc, N
2, xyz, G
3, pqr, G
4, efg, N

I cant find any solution to do this.

quote:
Originally posted by visakh16

its a straight forward update you want to do. Have a look at syntax of UPDATE statement in books online and try it yourself. In case you face any issues post the query and we will help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

You could have tried

Update Person
Set Category=case when category='G' then 'N' else 'G' end

Madhivanan

Failing to plan is Planning to fail

Not always safe without WHERE condition
what if there're other categories also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-21 : 04:19:13

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by crazycoder

Well, I can update any one of it using

Update Person
Set Category='G'
Where Category='N'

But I need to update all G to N also in the same query so that the the table should have the updated data as

1, abc, N
2, xyz, G
3, pqr, G
4, efg, N

I cant find any solution to do this.

quote:
Originally posted by visakh16

its a straight forward update you want to do. Have a look at syntax of UPDATE statement in books online and try it yourself. In case you face any issues post the query and we will help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

You could have tried

Update Person
Set Category=case when category='G' then 'N' else 'G' end

Madhivanan

Failing to plan is Planning to fail

Not always safe without WHERE condition
what if there're other categories also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Good point. You can add that condition in the WHERE clause

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources