There may be more elegant ways - but here goes:
If TABLE1 and TABLE2 are one for one in rows and ids then
UPDATE TABLE2 t2
SET NewField =
(SELECT OldField
FROM TABLE1 t1
WHERE t1.ID = t2.ID)
will work.
If there are missing rows/ids will cause NewField to be set to nulls.
Otherwise - create TABLE3 based on TABLE2 and use two insert/select
statements -
insert into table3 select t2.id, t1.oldfield, t2.fielda, t2.fieldb, ...
from table1 join table2 on t1.id = t2.id
insert into table3 select t2.id, t2.newfield, t2.fielda, t2.fieldb, ...
from table2 t2 where t2.id not in (select id from table1)
then drop table2 and rename table3 - a bit long-winded - maybe a SQL guru
can suggest a better way.
HTH
Mike
----- Original Message -----
From: "John Christenhusz" <[EMAIL PROTECTED]>
To: "Multiple recipients of list database" <[EMAIL PROTECTED]>
Sent: Monday, November 19, 2001 9:09 AM
Subject: [DUG-DB]: UPDATE
> G'day all,
>
> I want to copy a row of data from one table to another. Both tables are
> indexed with field name ID.
>
> What I want is to copy the data from Table1 field 'OldField' to Table2
field
> 'NewField'
>
> The following doesn't work, obviously the last WHERE clause sees only one
> column.
>
> UPDATE TABLE2
>
> SET NewField =
>
> (SELECT a.OldField
> FROM TABLE1 a, TABLE2 b
> WHERE a.ID = b.ID)
>
> WHERE TABLE1.ID = TABLE2.ID
>
>
> Any ideas how to do this?
>
> TIA
>
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
