After trying nearly every method put to me after a week i'm no closer at
getting around my problem of merging to tables together

the two tables are structured thus:

Rate99 (this holds all the records for the current year)
==========
Ref char(25)
Rval INT

Rate95A (this holds 1995 values which i would like inserted into the
above
table in a new field if the reference numbers of both match)
==========
Ref char(25)
Rv95a INT

They currently look like:

Rate99                                     Rate95A

Ref            Rval ('s)                Ref              RV95A('s)
===============              ===============
0000142   3000                       0057323     2300
0001425   2500                       0000142     2300
0014753   1800                       0014753     5000

as an example, in the real tables there are 1.7 million records.

What i want is one table based on the rate99 table but with values added
in a new field RV95A if the references are matched. Any values not used
in the RV95A table can be discarded when i drop the RV95A table, and any
RATE99 records that do not find a match should have 0 or null in the
RV95A field which indicates that the reference is a new one and couldn't
be found on the 1995 list.

Based on the above example, i want the rate99 (or a new table) to end up
looking like this:

Ref            Rval ('s)   RV95A ('s)
=========================
0000142   3000          2300
0001425   2500          null or 0
0014753   1800          5000

This is surely fairly straightforward SQL isnt it?

Btw i tried the outer join, it worked for 20mins and then locked solid.

This isnt the only problem i have, the other problem is no matter how i
try
the database (Paradigm.Gdb - INTERBASE 4) will not let me create a
primary
key on any table?

I'm very stuck as you can imagine lol. Cheers.

ANDY

On Thu, 11 Nov 1999 19:41:12 +0000, Andy Murphy

[...]

Unless I am missing something, this looks like a relatively simple UPDATEW
statement (you *did* mean to actually copy the columns values to the new
table, right?).

  UPDATE Rate99 T1
  SET T1.RV95A =
    (SELECT DISTINCT T2.RV95A
    FROM Rate95A T2
    WHERE T2.Ref = T1.Ref)

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

this comes up with SQL error, cant find t1.rv95a ??
do i have to set up alias's for t1 & t2

On Thu, 11 Nov 1999 20:58:55 +0000, Andy Murphy

If the table Rate99 does not already have this column for receiving the
data from the Rate95A table, you have to add it before executing the UPDATE
statement that updates that column.

  ALTER TABLE Rate99
  ADD COLUMN RV95A INTEGER

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

I'm gonna try using two tables because after being set about 20 ways to do
this, not one has worked.