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

post the DDL for the tables, someone will write the one query for you — Stephanie Page, Feb 08 '11 at 18:09
NEVER, EVER, EVER loop to process rows... ok, not never, sometimes you have to but it's super rare... like GOTO. — Stephanie Page, Feb 08 '11 at 18:10
Oh, and learn some SQL... it's an amazing language... one command with sooo much power. — Stephanie Page, Feb 08 '11 at 18:11
You can't use Interbase as a test database for a Oracle production database. There are many differences in the SQL they support. — , Feb 08 '11 at 19:03
BDE is pretty old and unsupported, if this is a new project, consider using a current and supported database connection layer, like dbExpress — jachguate, Feb 08 '11 at 19:48
Two sidesteps: you should try to avoid the BDE (if you use it, only use it for local file based databases like Paradox or dBase), and try to avoid `with` by putting the code in the `with` block into a function. OTOH: good question (+1); you should do this in SQL as the answers shown you. — Jeroen Wiert Pluimers, Feb 08 '11 at 19:55
BDE doesn't matter here. As long as the proper SQL is sent to the database you could even use a pidgeon to send the query and everything would be fine. Moreover dbExpress in Delphi 7 is full of bugs, and the Oracle driver a joke. SQLLINKS then worked far better. — , Feb 08 '11 at 22:09

d-live · Accepted Answer · 2011-02-11T19:40:05.197

2

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

edited Feb 11 '11 at 19:40

answered Feb 08 '11 at 18:16

d-live

7,926
3
22
16

Im alittle confused here. It would be ID for Master table and Master_Id for Reponses table. Is this still correct with your answer? – IElite Feb 08 '11 at 18:51
This does not work for me. It goes to exception on the ExecSQL command – IElite Feb 09 '11 at 02:45
General SQL Error - Multiple rows in singleton select. – IElite Feb 09 '11 at 16:16
That was to show you a way, it wasnt ment to be a runnable query. Updated answer with more clarification. – d-live Feb 09 '11 at 20:28
Well, i did state it was for interbase and orac;e. If you can get it so it works in Intgerbase, that will be suffice – IElite Feb 09 '11 at 22:39
even with your update, i still get the : "General SQL Error - Multiple rows in singleton select" – IElite Feb 10 '11 at 03:19
That means there are multiple response entries for a given master id. Depending on your business, you may want to use last response date in such cases, see update again. – d-live Feb 11 '11 at 19:36

score 0 · Answer 2 · answered Feb 08 '11 at 18:09

0

Try this SQL (changing names to fit your situation)

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

answered Feb 08 '11 at 18:09

Chris Hogan

868
6
9

as in my comment above, wouldn't this be: UPDATE Master m SET bank_cleared = ( SELECT bank_date FROM Responses WHERE master_id = m.id ) – IElite Feb 08 '11 at 18:54
Yes, I just wasn't 100% sure what your field names were. – Chris Hogan Feb 08 '11 at 19:14
@Chris - This does not work for me. It goes to exception on the ExecSQL command – IElite Feb 09 '11 at 02:47
Well, you should post the exception then :) Hard to guess it as long as it is on your machine... – Feb 09 '11 at 11:01
Yes, please. What is the exception? – Chris Hogan Feb 09 '11 at 12:58
General SQL Error - Multiple rows in singleton select. This message occurs for both Chris Hogan's and d-live's answers. – IElite Feb 09 '11 at 16:15
I'm guessing it's because the relation from Master to Responses is 1-to-many, so the subquery returns multiple values for bank_date but the main query is expecting a single value to assign to bank_cleared. You'll have to adjust the subquery to return a single date, for example change "SELECT bank_date" to "SELECT MAX(bank_date)". – Chris Hogan Feb 09 '11 at 18:08
Well, that worked without error, unfortunately, it filled in the master table's bank_cleared date with the max date. There were 1/26/11, 1/27/11, and 1/28/11 dates that should have been updated, but the field was populated with all 1/28/11 dates - thanks – IElite Feb 09 '11 at 18:18

Determining best method to traverse a table and update another table

2 Answers2