Hi-

I come from a SQL Server background and am just starting to use
Oracle. We are changing the names of data within a table and want to
copy data from the old record over to the new record. In SQL Server I
can use this

update a
set a.cpty_addr1 = b.cpty_addr1
from cpty a, cpty b
where a.cpty_cpty = 'ENGYAUTH' and b.cpty_addr1 = 'ENGYAUTH_S';

In Oracle this does not work. Could somebody please help me out?

Thanks

Try

update cpty
set cpty_addr1 = (select cpty_addr1 from cpty where cpty_addr1 =
'ENGYAUTH_S')
where cpty_cpty = 'ENGYAUTH';

However, it looks like if you made a mistake in you where clause. Should it
not be:

where a.cpty_cpty = 'ENGYAUTH' and b.cpty_cpty = 'ENGYAUTH_S'

If that is the case, then the update should be;

update cpty
set cpty_addr1 = (select cpty_addr1 from cpty where cpty_cpty =
'ENGYAUTH_S')
where cpty_cpty = 'ENGYAUTH';

Note: The select must return only one row.

David

"Andy" <drewb6911 (AT) yahoo (DOT) com> wrote