Updating a field in a table.. 2

[rahulroy08]

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.

 

[AlexCuse]

An update query should be all you need

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

 

[clapag22]

update goals
set emp_property = 'Westin'
where emp_property = 'The Westin'

 

[rahulroy08]

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.

 

[AlexCuse]

easiest way is using SELECT INTO. Like so

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

 

[LNBruno]

Something along the lines of:

SELECT *
INTO Goals_BAK
FROM Goals

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

 

[LNBruno]

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

 

[AlexCuse]

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

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

Ignorance of certain subjects is a great part of wisdom

 

[rahulroy08]

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.

 

[rahulroy08]

Hey Mike,

Thankyou as well. I'm really sorry.


Rahul.

 

[AlexCuse]

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

 

[rahulroy08]

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.

 

[AlexCuse]

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

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

 

[rahulroy08]

Hi Alex,

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

Thanks,


Rahul.

 

[AlexCuse]

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