Updating Partial Table Only - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Updating Partial Table Only

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-15 : 08:38:34

Hello,

I'm have a table with 1000 records in.
I have a view (with some of the same fields as the table, but not all) with 400 records in.

I want to replace the data in the table with the data from the view, but only for the 400 people listed in the view - I don't want the other 600 records touched.

Is this possible ?
If so, how should I approach this.
Does the fact that the table and the view have slightly different field make a difference ?

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-15 : 08:40:31

you can use WHERE clause in your UPDATE statement to constraint where are the records will be updated.

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 08:48:04

Here is one way:


UPDATE U
SET MyCol1 = 'xxx'
FROM MyTable AS U
     JOIN MyView AS V
        WHERE V.MyPK = U.MyPK

Kristen

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-15 : 08:50:08

Thanks Kristen,

So the Code you have provided - am I right in assuming I would need to repeat this for every field ?

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 08:52:14

Put all the fields you want to change in the SET section:


UPDATE U
SET MyCol1 = 'xxx',
    MyCol2 = 'yyy',
...
FROM MyTable AS U
     JOIN MyView AS V
        WHERE V.MyPK = U.MyPK

but just before you do that! mght be an idea to explain why you are doing this, and what you are wanting to change. Just in case we can suggest a better method etc.

Kristen

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-15 : 09:09:25

No problem.

Basically I have a table in my Database which lists all 1000 of my employees and their data (Name, Address, DOB etc).
However, for 400 employees, this data is out of date.

Now I do have the up to date information in another table which is what I want to import into the main Employee Table.
But, in this second table, I also have information for the other 600 employees, and this isn't up to date for them - that is the reason I can't just replace one table with the other.

Does this make sense, if not just shout up and I'll try to be more clear.

thanks,

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 11:41:01

OK, gotcha. Your propsed route sounds fine.

I'm not clear how the VIEW specifically gets you the rows with "good replacement data", but Hey! we don't need to wrorry about that, if you are confident it "marries" the right records you are good to go.

Backup first!

And if restoring from a Backup is not thinkable because this is a Live system IN ADDITION to a Backup make a "before" copy of the table. Here's what I do:

SELECT * INTO TEMP_Mytable_20071015 FROM MyTable

I always use the TEMP prefix and the date suffix. Then in a couple of months when I remember to do some housekeeping

I can Drop all the TEMP* tables that are more than a month or so old ...

Easy enough to "merge" data back from the TEMP table if the worst happens.

But take a Backup also!! [Did I say that already?!]

Kristen

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-16 : 03:20:35

Thanks Kristen - excellent advice.
I'll do that today.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources