Home » SQL & PL/SQL » SQL & PL/SQL » Insert statement incorrect
Insert statement incorrect [message #36262] Tue, 13 November 2001 11:19 Go to next message
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 Go to previous message
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)


----------------------------------------------------------------------
Previous Topic: Oracle Decode
Next Topic: Unique list without using DISTINCT
Goto Forum:
  


Current Time: Thu Mar 28 17:46:47 CDT 2024