Doubt: Consultation with insert, and upgrading? Is it possible?

[xShinmi]

Eae guys, good ... I was taking a look at the forum and I found it good ... hehe and I took the opportunity to post a doubt for you ...

is the following ...
For example I have the staff and other dependents ..
I want to get the dependents that have the name "Joao" and insert them as officials in the official table ... How! ! ??
How could I do this ??

vlw ... if it was kind of confused, eheh put again that I explain better ..

[dr_gori]

How is the employee and dependent table?

gives a song there for guys

[xShinmi]

Well .. actually was an analogy ... because in the real is much more complex than that hehe !!!

good then there goes ... I will explain the way that está ...
here in the company agent has a bank where the "lots" are registered that would be the terrain of the Houses ... and in every lot logically registered 1 or more address !!! That's why I wanted to get the lots that are registered with more than one address, create a new lot, and play this address in this batch. do in hand one by one I can, but what I wanted is to make a funcao, to find all the lots that have + 1 address creating a lot automatically and playing the address for this new lot ... so all lots with Only 1 address ...
AI goes the desc of tables:
where Lot_face would be the batch and address the address

Select all

lot_face: 
Name                                      Null?    Type 
----------------------------------------- -------- ---------------------------- 
FEAT_NUM                                  NOT NULL NUMBER(10) 
G_VERSION                                 NOT NULL NUMBER(10) 
G_NEXT_VERSION                                     NUMBER(10) 
G_GENERATION                                       NUMBER(10) 
CENTER_LINE_FNUM                          NOT NULL NUMBER(10) 
LOT_ID                                             NUMBER(10) 
LOT_TYPE                                  NOT NULL NUMBER(1) 
NUM                                                VARCHAR2(35) 
NUM_QTY                                            NUMBER(3) 
FEEDER_POINT                              NOT NULL NUMBER(1) 
ZIP                                                VARCHAR2(10) 
ATTENDANCE                                         NUMBER(1) 
REFERENCE_NAME                                     VARCHAR2(60) 
REFERENCE_TYPE                                     NUMBER(10) 
 
address: 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 ID                                        NOT NULL NUMBER(10) 
 LOT_FACE_FNUM                                      NUMBER(10) 
 STREET_ID                                 NOT NULL NUMBER(10) 
 SPECIAL_ADR_FNUM                                   NUMBER(10) 
 ADDR_TYPE                                          NUMBER(1) 
 NUM                                       NOT NULL VARCHAR2(35) 
 COMPL                                              VARCHAR2(35) 
 DEDICATED                                          NUMBER(1) 
 ROW_LOCK                                           NUMBER(10) 
 ROW_LOCK_TYPE                                      VARCHAR2(1) 
 G_VERSION                                 NOT NULL NUMBER(10) 
 G_NEXT_VERSION                                     NUMBER(10) 
 G_GENERATION                                       NUMBER(10)

Hehehe é this aí ...
If you need d + something tamoe aí!

[xShinmi]

Dae galera ...
good, so it's up I can do what I wanted ... I do not know if you understood what I was wanting but ... thank you
I I got the following ...
I did a function
In it I declared a cursor, I made the consultation I wanted
later I was giving an insert according to the information that I had in the cursor ...
vlwww
Fuizzz

[dr_gori]

I think you can do the direct insert:
See example:

Select all

INSERT  
INTO TABELA1 
( CAMPO1 
, CAMPO2 
, CAMPO3 
) 
(SELECT  
  X.CAMPOX1 
, X.CAMPOX2 
, X.NOME 
FROM OUTRA_TABELA X 
WHERE NOME LIKE 'JOAO%' 
) 
/ 

So, it firsts the select from below, and inserts everything in Table1. Without having to do a routine for that. (Sorry, I had not understood your doubt ...)


You can also use Merge, to do insert and update at the same time. (If there is, update, if it does not exist, insert). See this topic: http://en.glufke.net/oracle/viewtopic.php?t=305
: -O

[xShinmi]

our dr_gori this information was valiosissima ... !!!
é that now that I started to mecher with SQL in Oracle ... there is a lot of things to do ... muito good !!!
vlw aeee !!!
More tips ... are always welcome !!!!