how to update one table from another table?

[nat1967]

Hi everyone,

I have a database that holds account information on about 1000 accounts. I found out the hard way that the users should not be allowed to update the records themselves. Currently, the users have a form into which they type the account information and the changes required. I manually review this temp table, find the record in the production table, and update the production record to match the changes. Also, this temp table is used for inputing new accounts.

The new accounts are easy. I use a script with a Insert query written in SQL to move the new account information from the temp table to the "production" table.

The changes are a completely different problem. I have tried using an Update query but that doesnt work. I need the update query to use the account number in the temp table as its parameter to search the production table. Once found, update the fields. Therory sounded good but implementation was ... well... not good. I could never get it to work. So, my guess is that A2K doesnt support this method as Oracle does.

Does anyone have any suggestions on how to accomplish this change record problem I am having? any and all suggestions or solutions are greatly appreciated.

Thanks in Advance!!!! Have A Great Day!!!,

Nathan
Senior Test Lead

 

[KenReay]

HI

I cannot think of any reason why you cannot do this via an update query. A query joining you temp table and you production table on AccountId should do the trick, maybe you need to post the SQL to allow people to comment Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[shannonp]

Are you running the UPDATE query using a single value ([account number] on a form) or is this a complete production table update?

 

[nat1967]

Hi everyone,

Below is a small sample of my update sql.

Update tempTableCave
Set tempTableCave.[listed name] = tempTableClec.[listed name]
Where tempTableCave.[account number] = tempTableClec.[account number]

Shannon, the only form involved is the one that has the command button that executes the code. This is meant to update one table to another.

any suggestions are greatly appreciated. Have A Great Day!!!,

Nathan
Senior Test Lead

 

[nat1967]

Hey Guys and Gals,

I figured it out. User error...

I left out the second table from my sql. Thanks for you help and thoughts. It helped me re-focus and find the problem.

Have A Great Day!!!,

Nathan
Senior Test Lead