Insert statement incorrect [message #36262] |
Tue, 13 November 2001 11:19 |
Tony
Messages: 190 Registered: June 2001
|
Senior Member |
|
|
I'm running the statement below:
INSERT INTO assoc_mrns(mrn) a
SELECT distinct(mrn) from bics_temp_orders b
where a.mrn<>b.mrn;
I want to insert mrn from one table into a second without creating any duplicates in the second. The above gives me the following error:
where a.mrn<>b.mrn
*
ORA-00904: invalid column name
I also try an update, but that produces this:
update assoc_mrns a
set mrn = (select b.mrn from bics_temp_orders b
where a.mrn <> b.mrn);
ORA-01427: single-row subquery returns more than one row
Where is the inconsistency with my syntax on the insert statement? Thanks.
----------------------------------------------------------------------
|
|
|
Re: Insert statement incorrect [message #36263 is a reply to message #36262] |
Tue, 13 November 2001 11:41 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
insert
into assoc_mrns (mrn)
select distinct mrn
from bics_temp_orders b
where not exists
(select null from assoc_mrns a
where a.mrn = b.mrn)
----------------------------------------------------------------------
|
|
|