How to update multiple rows using single sql state

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

General SQL Server Forums

New to SQL Server Programming

How to update multiple rows using single sql state

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-01-28 : 02:51:05

Hai

I want to update mutiple rows using single statement.

Gender
M
F

Now I want to update M as Male and F as Female in Gender Table using single Sql Statement.

Can anyone help me please
Thanks in Advance...

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 03:00:08

UPDATE Table
SET Gender =CASE WHEN Gender ='M' THEN 'Male'
WHEN Gender ='F' THEN 'Female'
END

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-01-28 : 03:10:17

The problem with this is, its updating only one row.

I want to update both the rows at a time only.

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 03:14:59

quote:
Originally posted by soori457

The problem with this is, its updating only one row.

I want to update both the rows at a time only.

Suresh Kumar

One row? can you post sample data of your table & expected o/p?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 03:35:06

You maybe want to reset the ROWCOUNT variable?

SET ROWCOUNT 0

UPDATE Table
SET Gender = CASE WHEN Gender ='M' THEN 'Male'
WHEN Gender ='F' THEN 'Female'
ELSE 'Unknown'
END

E 12°55'05.25"
N 56°04'39.16"

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-01-28 : 04:07:31

THnks for ur replies

Gender(Coloumn Name)
M(data)
F(data)

With single sql statement, I want to update M as Male and F as Female

This is the required o/p I want

Gender
Male
Female

Suresh Kumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 04:11:17

And you have got them too.

E 12°55'05.25"
N 56°04'39.16"

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-01-28 : 04:57:31

UPDATE Table
SET Gender = CASE WHEN Gender ='M' THEN 'Male'
WHEN Gender ='F' THEN 'Female'
ELSE 'Unknown'
END

with this query, I'm updating only one row
But I want both rows should be updated simlutaneously with single sql statement

Suresh Kumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 05:05:36

You are updating complete table!
Try the query and you will find out.

You need to learn set-based thinking...

E 12°55'05.25"
N 56°04'39.16"

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-01-28 : 05:51:16

Yah, I have tried it, But I'm not getting, only one row is updating and the other one is deleted.
When I run the above query, I'm getting the o/p as

Gender
Male

First of all, tell me, is it possible to update mutliple rows of same column simultaneously.

Suresh Kumar

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-01-28 : 06:10:57

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 06:39:57

quote:
Originally posted by soori457

Yah, I have tried it, But I'm not getting, only one row is updating and the other one is deleted.
When I run the above query, I'm getting the o/p as

Gender
Male

First of all, tell me, is it possible to update mutliple rows of same column simultaneously.

YES IT IS POSSIBLE!
And there is NO WAY records can be deleted from ANY of the statements posted above.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-28 : 08:42:54

What is the size Gender column?

See if select works

Select Gender, case when gender='M' then 'Male' when gender='F' then 'Female' end as new_gender from table

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-28 : 11:46:39

Even better, follow the FIRST LINK in my signature. Read the ENTIRE page. Then, follow the directions on the page. You will get an answer VERY fast that way.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-28 : 11:53:09

[code]
-- lets create our table
create table Gender ( GenType varchar(15))

-- lets fill our table with data
insert into Gender

select 'M' union all
select 'M'union all
select 'F'union all
select 'F'

-- show what is in the table
SELECT * from Gender

OUTPUT:
M
M
F
F

-- run our update

UPDATE Gender
set GenType = CASE GenType WHEN 'F' THEN 'Female' WHEN 'M' THEN 'Male'
ELSE 'Unknown' END

-- show what is in the table
SELECT * from Gender

OUTPUT:
Male
Male
Female
Female

--dump this crap

drop table Gender[/code]

It works for me. Dunno what your schema is like.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-01-29 : 01:19:06

Thanks for ur reply

Its working

Suresh Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-29 : 03:41:43

quote:
Originally posted by soori457

Thanks for ur reply

Its working

Suresh Kumar

How is it working now?
What was wrong with your previous runs?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources