Need help with copying data...

[tthdoc]

I need to copy data and want to make sure I do it correctly.  To make it easy to understand what I am trying to do, I have 2 tables.  Table1 has 2 fields -  ID and Name.  Table2 has 4 fields - ID, First, Middle, Last.  What I want to do is copy the data from the Name field on table1 and put it in the First field on table2.  The same ID numbers are in both tables right now.  When I look at the two tables I see as example this:

 

Table1

ID  Name

2    John

4    Mary

5    Joe 

 

Table2

ID  First  Middle Last

1                        Jones

2                        Smith

3                        Jackson

4                        Gold

5                        Ray

 

What I don't want is to end up with  ID1 being John Jones ID2 being Mary Smith and ID3 being Joe Jackson.  In other words, since the id's order is different in the two tables, I want to make sure they are matched so I end up with ID1 still is just Jones, ID2 is John Smith, ID3 is just Jackson, ID4 is Mary Gold, and ID5 is Joe Ray.

 

Thanks for the help in advance,

Doc

 

 

 

 

[requinix]

Do an UPDATE using a JOIN or a subquery.

[tthdoc]

Thanks for the quick reply.  I was actually looking for exactly how to phrase the query,  my database knowledge is limited.  Could you be more specific on how to do it? 

 

Thanks again

Doc

[requinix]

UPDATE Table2
SET First = (SELECT Name FROM Table1 WHERE Table1.ID = Table2.ID LIMIT 1)
WHERE ID IN (SELECT ID FROM Table1)

I'm pretty sure you could do a JOIN (like you would with a SELECT) but I've been using too much MS SQL lately to remember.

[tthdoc]

Thanks so much, that worked perfect.

Doc