Assuming this is best done with a SQL command, I'm looking for some help writing the SQL for it. Since you already have these tables linked, you only need an SQL select to obtain the information anytime it is needed.However, if you insist on this route, there are some other things to be aware of. With that, there is no built in storage for this type of statement so you would need to keep a copy of the statement somewhere else like a text file for when needed. Since you appear to be new to all this, it may be beyond you current knowledge. This needs modification based on the "Balance History" table as it is unclear as to which record for the Account is to be retrieved for the balance: UPDATE "Accounts" SET "Curr Value" = (SELECT "Balance" FROM (SELECT "Acct No", "Balance" FROM "Balance History", (SELECT "Acct No", MAX("Balance Date") SELDATE FROM "Balance History" GROUP BY "Acct No") A WHERE "Balance History"."Acct No" = A."Acct No" AND "Balance History"."Balance Date" = A.
SELDATE) B WHERE "Accounts"."Acct No" = B."Acct No") EDIT 9/28/2017: The previous UPDATE statement bothered me enough to post a much preferred solution which can be retrieved anytime and is not copied into the main 'Account' record.
Additionally, since it is a query, it allows the statement to be stored in the Query section.
So when might you need to copy data from one table to another?
Consider a help desk application that allows support staff to create support tickets, where each ticket in the system exists as a record in the Tickets table. To ease data entry, let's say that administrative users can create "ticket templates," which pre-define the values for a number of ticket data points that are stored in a table named Ticket Templates.
Consequently, to prevent this, you might do this: One potential issue may arise when the subquery actually finds more than one matching row in tbl B.
If this happens, the UPDATE will terminate with an error ("subquery may return only one row"). works fine when I try to update all the records in tbl A, however, in this case I only have missing data which I have identified and populated in tbl B.When I try to update only the missing data the non-matched data is also updated but to NULL. The reason some of your rows are getting NULLs updated is due to the subquery.Same result using a join in a SELECT statement: SELECT "Accounts".*, B."Balance" FROM "Accounts" LEFT JOIN (SELECT "Acct No", "Balance" FROM "Balance History", (SELECT "Acct No", MAX("Balance Date") SELDATE FROM "Balance History" GROUP BY "Acct No") A WHERE "Balance History"."Acct No" = A."Acct No" AND "Balance History"."Balance Date" = A. I understand your advice against keeping balances this way, but you can rest assured that we have reasons for maintaining a history of the account balances.SELDATE) B ON "Accounts"."Acct No" = B."Acct No" Thanks, Ratslinger for your quick response! I don't see, though, how this statement finds the most RECENT record to use to update the Accounts table. That's why I created a separate table to keep them in. I've posted a number of examples of this - here is one. You can basically do that with an SQL select - almost the one I presented.Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. Update data in table A based on two or more common columns in table B.