SEO

#1 (**permalink**) 02-24-2008, 12:51 PM

I have two tables table A and table B I want to get the audit_date
from table b into the field date_reported on table A where the values
in the field skey_ref on table B match the field skey on table A.

I have tried using this statement but have no luck. Any help would be
greatly appreciated.

update tableA set date_reported = (select audit_date from tableB,
tableA
where tableB.skey_ref = tableA.skey)

Returns: ORA-01427: single-row subquery returns more than one row

#2 (**permalink**) 02-24-2008, 12:52 PM

jmpXor wrote on 22.04.2005 22:31:
> I have two tables table A and table B I want to get the audit_date
> from table b into the field date_reported on table A where the values
> in the field skey_ref on table B match the field skey on table A.
>
> I have tried using this statement but have no luck. Any help would be
> greatly appreciated.
>
> update tableA set date_reported = (select audit_date from tableB,
> tableA
> where tableB.skey_ref = tableA.skey)
>
> Returns: ORA-01427: single-row subquery returns more than one row
most probably this should be:

update tableA
set date_reported = (select audit_date from tableB
where tableB.skey_ref = tableA.skey)

(Note the missing tableA in the subselect)

Thomas

#3 (**permalink**) 02-24-2008, 12:52 PM

> update tableA
> set date_reported = (select audit_date from tableB
> where tableB.skey_ref = tableA.skey)
where exists(select 1 from tableB
where tableB.skey_ref = tableA.skey)

Cheers
Serge

PS: Also look at MERGE
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

UPDATE using multiple tables