Update one table with data from another

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

General SQL Server Forums

New to SQL Server Programming

Update one table with data from another

Author

Topic

Redser
Starting Member

9 Posts

Posted - 2012-01-31 : 13:42:06

Hi Folks I'm after some help from you

I have one table that contains Customer & Application Details and my goal is to separate this data into two tables thus removing the need to record the Customer’s details more than once. I have managed to copy unique Customers details into a separate table based on Surname, Forename and DOB and assigned them a PK based on auto number.
What I am no trying to do is to insert thisnew Customer_ID into the original Application table (It previously didn’t have a customer ID). Each Customer can have many applications

My means of linking individual Customers to one or more Applications is to compare Surname, Forename and DOB in each table. Once I’ve managed to insert this I’ll remove the Customers details from the Applications table.

My tables are Applications & tbl_Customer and here is one approach I’ve attempted but with no success. Any help would be much appreciated.

UPDATE Applications
SET Customer_ID = (Select tbl_Customer.Customer_ID from tbl_Customer

WHERE Customer.Surname = Application.Surname and Customer.Forename = Applications.Forenames and Customer.DOB = Applications.DOB)

WHERE EXISTS
(Select tbl_Customer.Customer_ID from tbl_Customer
where Customer.Surname = Application.Surname and Customer.Forename = Applications.Forenames and Customer.DOB = Applications.DOB)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 14:22:11

[code]
UPDATE a
SET a.Customer_ID = c.CustomerID
FROM Applications a
INNER JOIN Customer c
ON c.Surname = a.Surname
AND c.Forename = a.Forename
and c.DOB = a.DOB
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Redser
Starting Member

9 Posts

Posted - 2012-01-31 : 17:46:25

Cheers Visakh16 that worked a treat. I guess I should keep to the K.I.S.S. principle

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 19:07:21

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources