how to update all rows in a table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi all,
I am using SQLServer2008. I created table as Student and inserted some rows,

SID   Sname   SFEE($)  SADDRESS 
01    thniks  2000     chennai,india
o2    madhuri 2100     newdelhi,india
03    sanjeev 1980     devas,india,
 ,     , ,     ,        ,,,,,,,,
 ,     , ,     ,        ,,,,,,,,
 ,     , ,     ,        ,,,,,,,,
1000  dhanu    3000    newyork,USA

now I want to update SADDRESS of all Students, now i am looking for how to change
SADDRESS (here SADDRESS as different of all Students and also update SADDRESS also ). I am updating each and every row by using update command. It is very time consuming to update each and every row.
Please any one help to me is there another procedure to update that SADDRESS rows at a time.

Thanks in advance,
Thanks and regards
Naresh.

Posted 1-May-12 18:08pm

Unareshraju

Updated 2-May-12 2:53am

Shahin Khorshidnia

v3

Add a Solution

Comments

VJ Reddy 2-May-12 3:37am

Please post your further queries in the comment section of the concerned solution or use improve question wizard if you want to improve the question.
I have moved the info posted by you in Solution 2 to comments section of Solution 1.
Thank you.

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)

VJ Reddy · Answer 1 · 2012-05-01T18:29:00

If you want to update the SADDRESS of particular rows then, WHERE clause can be used like

SQL

UPDATE Student
SET SADDRESS = 'chennai,india' WHERE SID IN (1,2, 10)
GO

here I assumed that SID is of type int and used IN clause. Instead of that any WHERE condition can be used as required.
If SADDRESS in all rows is to be set then

SQL

UPDATE Student
SET SADDRESS = 'chennai,india'
GO

can be used.

[Edit]The following added to update the rows of table from another table [/Edit]

If address A in all rows is to be replaced address B,
address C with address D and like, then a Temporary table can be created and populated with the old address and corresponding new address.
Then the rows can be updated as shown below

SQL

--Create a Temporary table with appropriate data types
CREATE TABLE NewValues (OldAddress NVARCHAR(25), NewAddress NVARCHAR(25))
--Insert old address and the corresponding new address
INSERT INTO NewValues
VALUES  ('chennai,india','mumbai,india'),
	('Add1','Address One'),
        ('Add2', 'Address Two')
--Check whether they are correctly inserted
SELECT * FROM NewValues
--Update the Student table such that for all rows where the 
--SADDRESS is equal to the OldAddress in NewValues (Temporary) table
--replace the SADDRESS in Student with NewAddress from NewValues table 
UPDATE Student
SET Student.SADDRESS  = NewValues.NewAddress
FROM NewValues
INNER JOIN  Student 
ON Student.SADDRESS  = NewValues.OldAddress
--Delete the temporary table
DROP TABLE NewValues
--Check whether replacement was done properly
SELECT * FROM Student

Unareshraju · Answer 2 · 2012-05-03T22:17:00

Solution 3

by using case in storeprocedure i was solved this

Posted 3-May-12 22:17pm

Unareshraju