Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating a field in a table.. 2

Status
Not open for further replies.

rahulroy08

Programmer
Jul 3, 2007
58
US
Hi,

I'm new to SQL Server and I'm currently using SQL Server 2005.

In my database, I have a field in EMP_Property in a table named GOALS. But unfortunately there are some entires as 'The Westin' instead of 'Westin' and I need to update all the records with 'The Westin' to 'Westin'.

Can someone help me with this.

Thanks,


Rahul.
 
An update query should be all you need

Code:
update GOALS
set EMP_Property = 'Westin'
where EMP_Property = 'The Westin'

Make sure you have a backup before testing.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom
 
update goals
set emp_property = 'Westin'
where emp_property = 'The Westin'

 
Hi Alex,

As you suggest I guess having a backup helps.But how do I create a backup of the table before I test this.


Please suggest.

Thanks,


Rahul.
 
easiest way is using SELECT INTO. Like so

Code:
select * into MyBackupTable
from Goals

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom
 
Something along the lines of:

Code:
SELECT *
INTO Goals_BAK
FROM Goals



< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill
 
Yeah! What Alex said!

You know, for someone who doesn't do any programming whatsoever, he sure is fast!

;-)

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill
 
I don't even use SQL, it is for wizard driven programmers ;-)

I am just working on a series of books online flash cards so have memorized much of it. I plan to have the level one cards out by christmas, for only one million dollars
225px-Dr_Evil.jpg


[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom
 
Thanks Alex,

It worked. But there are some duplicates which I want to eliminate. Can you please help me how to do that.



Thanks,


Rahul.
 
If by duplicates, you mean every single column has the same value in >1 row, then a DISTINCT ought to do it.

If some columns are the same, and others are different, then please post some sample data and what you would like to see as the result.

Hope this helps,

Alex



[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom
 
Yes it is the first case, that I'm facing with. Already there is a record with the same data with the property as 'Westin'.

But now when I changed the records with the property from 'The Westin' to 'Westin',I have another record with the same data.

How do I eliminate this.


Rahul.


 
A quick way would be to do another select into , this time using the DISTINCT keyword.

Code:
select distinct * into SomeTable
from Goals


You could then clear out your goals table and insert everything from the 'distinct' table into there.

Be careful doing anything like this, as I am sure I don't need to explain to you the consequences if you lose some data!

Good Luck

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom
 
Hi Alex,

How do I clear all the data from the table 'GOALS' once I copy the data into a temp table.

Thanks,


Rahul.

 
Look in Books Online (SQL Server help) at entries for DELETE and TRUNCATE.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top