Determining best method to traverse a table and update another table

Question

I am using Delphi 7, BDE, and Interbase (testing), Oracle (Production).

I have two tables (Master, Responses)

I need to step through the Responses table, use its Master_Id field to look it up in Master table (id) for matching record and update a date field in the Master table with a date field in the Responses table

Can this be done in SQL, or do i actually have to create two TTables or TQueries and step through each record?

Example:

Open two tables (Table1, Table2)

with Table1 do
begin
 first;
 while not EOF do
 begin
  //get master_id field
  //locate in id field in table 2
  //edit record in table 2
  next;
 end;
end;  

thanks

Answer 1

One slight modification to Chris' query, throw in a where clause to select only the records that need the update. Otherwise it will set the rest of the dates to NULL

UPDATE Master m
SET 
    m.date = (SELECT r.date FROM Reponses r WHERE r.master_id = m.id) 
WHERE m.id IN (SELECT master_id FROM Responses)

Updated to use aliases to avoid confusion which col comes from which table. This is not ready made, copy-past'able query as UPDATE syntax differs from database to database. You may need to consult your database sql reference for JOIN in UPDATE statement syntax.

When there are multiple responses to same master entry

UPDATE Master m 
SET      m.date = (
    SELECT MAX(r.date) FROM Reponses r WHERE r.master_id = m.id)  
WHERE m.id IN (SELECT master_id FROM Responses) 

I used MAX() you can use whatever suits your business. Again invest some time understanding SQL. Its hardly a few days effort. Get PLSQL Complete reference if you are into Oracle

Answer 2

Try this SQL (changing names to fit your situation)

UPDATE Master m SET date = ( SELECT date FROM Responses WHERE id = m.id )