Home » SQL & PL/SQL » SQL & PL/SQL » copy view to table with conditions (Oracle)
copy view to table with conditions [message #358334] Mon, 10 November 2008 14:38 Go to next message
armandoforums
Messages: 1
Registered: November 2008
Location: Portugal
Junior Member
Hi people,this is the first time i write in this forum and I'm new with oracle technologies, so i hope u can help me Smile

I'm trying to copy data from one table to other:

insert into table1 values(id, nif, name, other) from table2

but the field nif in the table2 sometimes is repeated and i need that only the first in table one.

I already try;
insert into table1 values(id, distinct(nif), name, other) from table2

or
insert into table1 values(id, nif, name, other) from ( select id, distinct nif, name, other from table2)
but didn't work :s

So can u help me understand what is my mistake?

Thanks for the attention Smile
Re: copy view to table with conditions [message #358337 is a reply to message #358334] Mon, 10 November 2008 14:52 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
i need that only the first in table one

In that case, you have to tell Oracle what you want. It can be done by using the WHERE clause. It should contain a condition which will uniquely retrieve a desired record.

Can you find such a criteria? "The first one" might be several different things, but as long as you know which one is the first one, no problem. It may be MAX(nif), MIN(nif), the first if ordered by date or some sequence number, etc.
Re: copy view to table with conditions [message #358338 is a reply to message #358334] Mon, 10 November 2008 14:54 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@armandoforums,

Try to go through INSERT Statement.
Also concentrate on INSERT SELECT in this link.

Next time before posting, please read OraFAQ Forum Guide.

Hope this helps.
Regards,
Jo
Previous Topic: to_number in view
Next Topic: ERROR - Buffer overflow, limit of 1000000 bytes
Goto Forum:
  


Current Time: Wed Dec 07 07:03:07 CST 2016

Total time taken to generate the page: 0.21127 seconds