Best way to do this?? 1

[EdLentz]

I have a table with customer information, along with other fields. I have another table that I want to get information (several fields) from the previous table into. Should I use an insert statement, an update statement or a join?

TIA

Ed

 

[TonyGroves]

Can you show us your table structures, and let us have more information abouty your requirements?

 

[EdLentz]

Sure,
The first table called cust has the usual fields, clientid(autonumber), name, address, city state, zip, etc. The second table is a workorder table with fields named the same. I have a field in workorder that has the clientid in it. What I want to do is key on the clientid and copy the info from the customer table into the workorder table. I have about 800 records that I need to change. Does this help?

Thanks
Ed

 

[TonyGroves]

You could use:
[tt]
update cust c join workorder w using (clientid)
set
w.name=c.name,
w.address=c.address,
w.city=c.city,
w.state=c.state,
w.zip=c.zip
[/tt]

 

[EdLentz]

Tony

That looks like it would work, except that I was getting ready for work this morn when I posted. The field in the customer table is clid and the workorder field is clientid. I guess I could change it for the few minutes it would take to do this. I'll try it tonight

Thanks!

Ed

 

[TonyGroves]

Then try:
[tt]
update
cust c join workorder w on c.clid=w.clientid
set
w.name=c.name,
w.address=c.address,
w.city=c.city,
w.state=c.state,
w.zip=c.zip
[/tt]

 

[EdLentz]

With a little additions to other fields that works GREAT!
Thanks alot Tony! Have a good evening!

Ed