Link to home
Start Free TrialLog in
Avatar of clifford_m71
clifford_m71Flag for United States of America

asked on

Changing a name with SQL Update statement

I have a SQL 2005 database that runs our accounting software. When invoices are created the data goes into a historical table (we'll call it INVOICE) that, obviously, we can pull from at any point in the future. My question pertains to one column, the CustomerName. Let's say I have a customer called "ABC" and I have invoiced them for the past serveral years then on 10/15/2012 they change their name to "DEF". Now the invoice table will show two names for this customer. "ABC" for all invoices prior to 10/15/2012 and "DEF" for all after. What script can I run that will replace the old name of the customer with the most recent name in the database? Something along the lines of UPDATE INVOICE set CustomerName = CustomerName of the most recent entry?

Thanks for the help, hope I explained his well enough.
Avatar of G Trurab Khan
G Trurab Khan
Flag of Pakistan image

Do you have any unique key identifying both of these customers as a single customer like CustomerNo
Avatar of clifford_m71

ASKER

There is a customer number but what I want is something that will essentially search the database for instances where the customer name is different at the most recent date (we can use the document date here) then it was at previous dates then change the name at those previous dates to the most recent. Make any sense??
As Ghunaima points out,  you need to be able to indentify which customers have been invoiced with different names. Perhaps you can provide some sample data along with the relevant table structures (e.g. customerid, invoice date, etc.).
It would be a bad practice to change the name on invoices sent to the old company name.  If for some reason you have to produce an invoice from 10 years ago and it has the name that didn't exist until this year, that just looks wrong.  I'm pretty sure that would fail standard accounting practices.  You need to find another way to relate the new and the old.
ASKER CERTIFIED SOLUTION
Avatar of G Trurab Khan
G Trurab Khan
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Worked perfect, thanks. It's a long story as to why the names change and why I run reports by customer name and not number but the bottom line is you just saved me a lot of time each month/quarter.