Update a table with data from another table

[SuperComputing]

Forgive me if I've posted this in the wrong forum...

We have a Database 'CP' with several tables, one of which is 'Customers'. We went into the 'Customers' table and made changes to both the 'SalesRep' column and the 'Category' column. Now, 45 days later, they want the changes made to the 'SalesRep' column to be undone.

I have a backup of the table before any changes were made and I have restored it to 'CustomersOLD' and have gone in and updated the 'Category' column and left the 'SalesRep' column alone. However, the current table has had 300+ new customers added and several hundred of the rows of the existing customers have been modified. I can't simply import the new customers into the old table and replace.

I don't know if the syntax exists, can I combine the 2 tables, based on the 'CustomerNumber' column, and update the existing 'SalesRep' column with the data that is in the 'CustomersOLD' table, 'SalesRep' column?

I just don't know the syntax.
UPDATE Customers SET 'SalesRep' = 'CustomersOLD.SalesRep' WHERE 'Customers.CustomerNumber' = 'CustomersOLD.CustomerNumber'

Since the 'CustomerNumber' is incremental, do I need to run a FOR loop?

Thanks for all of your help!

 

[kaht]

Just use a simple join on your update statement. Here's an example:

[COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (a [COLOR=blue]int[/color], b [COLOR=blue]char[/color](3))
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @t
[COLOR=blue]select[/color] 1, [COLOR=red]'abc'[/color] union
[COLOR=blue]select[/color] 2, [COLOR=red]'def'[/color] union
[COLOR=blue]select[/color] 3, [COLOR=red]'xyz'[/color]

[COLOR=blue]declare[/color] @tOld [COLOR=blue]table[/color] (a [COLOR=blue]int[/color], b [COLOR=blue]char[/color](3))
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @tOld 
[COLOR=blue]select[/color] 1, [COLOR=red]'ABC'[/color] union
[COLOR=blue]select[/color] 2, [COLOR=red]'DEF'[/color] union
[COLOR=blue]select[/color] 3, [COLOR=red]'XYZ'[/color]

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @t

[COLOR=blue]update[/color] t1
[COLOR=blue]set[/color] t1.b = t2.b
[COLOR=blue]from[/color] @t t1
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] @tOld t2
   [COLOR=blue]on[/color] t2.a = t1.a

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @t

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[SuperComputing]

Kaht,

Thanks for getting me pointed in the right direction, although I couldn't get it to work, it did make me read-up on the right subjects.

Here's what I wound up using and it worked. (after 5 hours)

UPDATE Customer  
SET SalesRep = ( SELECT CustomerOLD.SalesRep
FROM CustomerOLD
WHERE CustomerOLD.CustomerNumber = Customer.CustomerNumber) 
WHERE EXISTS
  ( SELECT CustomerOLD.SalesRep
    FROM CustomerOLD
    WHERE CustomerOLD.CustomerNumber = Customer.CustomerNumber )

From what I see from the results, it worked like a left outer join would, except that it didn't null out the values that it didn't find. That alone saved us from having to manually update 350 of the 16K customers.

Thanks again for all of your help!

 

[gmmastros]

SuperComputing,

Is this a 'one off' situation where you are running this query just once, or is this code that will be run periodically? The reason I ask is because there is likely to be a more efficient query that will produce the same results. If this is a once and done query, then it really doesn't matter.

Also, since you got some help from kaht, I would encourage you to scroll up to his post and click the link (at the bottom of his post) that says:

Thank kaht
for this valuable post!

This is a special way of marking a post as being helpful.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SuperComputing]

One time deal, (I surely hope), thanks for asking.

And kaht has been graciously thanked

 

[SQLSister]

Yes, this syntax works but it could be slower in general than using a join.

"NOTHING is more important in a database than integrity." ESquared

 

[PatriciaObreja]

Try like this:

UPDATE Customers SET SalesRep = 
(Select SalesRep From CustomersOld WHERE Customers.CustomerNumber = CustomersOLD.CustomerNumber)

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[SuperComputing]

Thanks Patricia,

I tried that in a development database and while it does work, the code that I wound up using (above) didn't write null values to rows that it did not find, it simply left them alone. Which turned out to be well worth the time.

Thanks to all again!