SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to update multiple rows using single sql state
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

soori457
Yak Posting Veteran

India
85 Posts

Posted - 01/28/2008 :  02:51:05  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/28/2008 :  03:00:08  Show Profile  Reply with Quote
UPDATE Table
SET Gender =CASE WHEN Gender ='M' THEN 'Male'
WHEN Gender ='F' THEN 'Female'
END
Go to Top of Page

soori457
Yak Posting Veteran

India
85 Posts

Posted - 01/28/2008 :  03:10:17  Show Profile  Reply with Quote
The problem with this is, its updating only one row.

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

Suresh Kumar
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/28/2008 :  03:14:59  Show Profile  Reply with Quote
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?

Edited by - visakh16 on 01/28/2008 03:15:16
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/28/2008 :  03:35:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

soori457
Yak Posting Veteran

India
85 Posts

Posted - 01/28/2008 :  04:07:31  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/28/2008 :  04:11:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And you have got them too.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

soori457
Yak Posting Veteran

India
85 Posts

Posted - 01/28/2008 :  04:57:31  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/28/2008 :  05:05:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

soori457
Yak Posting Veteran

India
85 Posts

Posted - 01/28/2008 :  05:51:16  Show Profile  Reply with Quote
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
Go to Top of Page

soori457
Yak Posting Veteran

India
85 Posts

Posted - 01/28/2008 :  06:10:57  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/28/2008 :  06:39:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 01/28/2008 06:40:51
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 01/28/2008 :  08:42:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 01/28/2008 :  11:46:39  Show Profile  Reply with Quote
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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 01/28/2008 :  11:53:09  Show Profile  Reply with Quote

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


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
Go to Top of Page

soori457
Yak Posting Veteran

India
85 Posts

Posted - 01/29/2008 :  01:19:06  Show Profile  Reply with Quote
Thanks for ur reply

Its working


Suresh Kumar
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 01/29/2008 :  03:41:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000