By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,225 Members | 1,434 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,225 IT Pros & Developers. It's quick & easy.

Normalizing with Update

Expert 100+
P: 164
Hello, I have run into an issue and need someone to explain a process to me in laymen's terms so I can understand it.

I have two [hypothetical] tables in MS Access. They are define thus:

Expand|Select|Wrap|Line Numbers
  1. Table: Products
  2.  
  3. ID      Name    Customer
  4. -------+-------+-------
  5. 1       Pants   Bob
  6. 2       Shirt   Henry
  7. 3       Shoes    Dad
  8. 4       Socks   Dad
  9. 5       Apple   Henry
  10. 6       Banana  Henry
  11. 7       Orange  Henry
  12.  
  13. Table: Customers
  14.  
  15. ID      Name 
  16. -------+-------
  17. 1       Bob
  18. 2       Henry
  19. 3       Dad
  20. 4       Jacob
  21.  
Please help me understand the SQL statement I could write in order to update 'Products' by replacing the data in the Customer column with the data in the ID column of 'Customers' which corresponds.
Apr 15 '09 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Something like the following:
Expand|Select|Wrap|Line Numbers
  1. UPDATE Products INNER JOIN Customers ON Products.Customer = Customers.Name SET Products.CustomerID = Customers.ID;
  2.  
where Products.CustomerID is a new field with a relevant datatype (I guess Products.Customer and Customers.ID datatypes don't match)
Apr 15 '09 #2

NeoPa
Expert Mod 15k+
P: 31,707
First add a new field into your Products table called CustomerID. This would be of the same type as the [ID] field already in the Customers table, except that if Customers.ID is an AutoNumber field, CustomerID would have to be set as Long Integer.

That done, you would use SQL very similar to (identical with really) that which Fish has supplied already :
Expand|Select|Wrap|Line Numbers
  1. UPDATE Products INNER JOIN Customers
  2.     ON Products.Customer=Customers.Name
  3. SET    Products.CustomerID=Customers.ID
Apr 15 '09 #3

Expert 100+
P: 164
Delicious, thank you!
Apr 16 '09 #4

NeoPa
Expert Mod 15k+
P: 31,707
You're welcome :)

Anyone looking to normalise existing data is worth supporting as far as I'm concerned.
Apr 16 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.