RE: Mass updates from one table to another

Assuming your doing the following:
  insert into new ... select ... from old

In your select, rename the column.. Eg:

insert into new
  select
    ...,
    PWD_EXPIRE_DATE "PWDEXPIRE_DATE"
  from
    old;

Hope this helps..

Shawn M Ferris
Oracle DBA - Time Warner Telecom

> -----Original Message-----
> From: Yexley Robert D SSgt AFIT/SCA [mailto:Robert.Yexley_at_afit.af.mil]
> Sent: Friday, July 21, 2000 2:22 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Mass updates from one table to another
>
>
> Hello everyone,
>
> OK, I was hoping this would be a really simple update, and
> that I could
> knock it out real quick. After working on it unsuccessfully
> for three hours
> now, I'm beginning to worry about whether or not I'm prepared for my
> upcoming OCP test, heh. I was hoping somebody could help me
> with the best
> syntax for making this update.
>
> OK, I have two tables in my database, that really don't need
> to be separate
> tables. I have decided to correct that problem, and combine
> the tables, and
> then update the resulting table with the data from the
> previous table. Here
> is what the two tables originally looked like:
>
> AFITNET_USER_NAME
> Name Null? Type
> ------------------------------- -------- ----
> SSAN NOT NULL VARCHAR2(11)
> LOGIN_NAME NOT NULL VARCHAR2(30)
> INPUT_DATE NOT NULL DATE
> USER_NAME NOT NULL VARCHAR2(30)
> USER_UID NUMBER(6)
> HOST_ACCNT_CREATED VARCHAR2(1)
> UPDATE_DATE DATE
> UPDATE_LOGIN_NAME VARCHAR2(30)
>
> AFITNET_USER_NAME_DETAILS
> Name Null? Type
> ------------------------------- -------- ----
> SSAN NOT NULL VARCHAR2(11)
> USER_NAME NOT NULL VARCHAR2(10)
> BUILDING_CODE VARCHAR2(4)
> ROOM_CODE VARCHAR2(5)
> USER_TYPE VARCHAR2(1)
> CLASS VARCHAR2(10)
> PWDEXP_CODE VARCHAR2(1)
> MONTH VARCHAR2(3)
> YEAR VARCHAR2(4)
> PWD_EXPIRE_DATE DATE
> FORM_35_SIGNED VARCHAR2(1)
> SIGNATURE_DATE DATE
>
> I got rid of unused columns from AFITNET_USER_NAME_DETAILS,
> and put the
> remaining ones that are used into the AFITNET_USER_NAME table
> (and added a
> few that it didn't have before too), and we now have one
> table that looks
> like the following:
>
> AFITNET_USER_NAME
> Name Null? Type
> ------------------------------- -------- ----
> SSAN NOT NULL VARCHAR2(11)
> LOGIN_NAME NOT NULL VARCHAR2(30)
> INPUT_DATE NOT NULL DATE
> USER_NAME NOT NULL VARCHAR2(30)
> USER_UID NUMBER(6)
> HOST_ACCNT_CREATED VARCHAR2(1)
> UPDATE_DATE DATE
> UPDATE_LOGIN_NAME VARCHAR2(30)
> BUILDING_CODE VARCHAR2(4)
> ROOM_CODE VARCHAR2(5)
> USER_TYPE VARCHAR2(1)
> CLASS VARCHAR2(10)
> PWDEXP_CODE VARCHAR2(1)
> PWD_EXPIRE_DATE DATE
> FORM_35_SIGNED VARCHAR2(1)
> SIGNATURE_DATE DATE
> SYSTEM_TYPE_CODE VARCHAR2(1)
> IA_DUTY_CODE VARCHAR2(1)
>
> My problem now is this. I need to get the PWD_EXPIRE_DATE
> values from the
> AFITNET_USER_NAME_DETAILS table, into the corresponding column in the
> updated AFITNET_USER_NAME table, for each common SSAN in that
> table. I
> can't seem to figure out the correct syntax to make this
> work. Any thoughts
> or input would be greatly appreciated. Thanks in advance.
>
> SSgt Robert D. Yexley
> Air Force Institute of Technology
> Applications Services Division (SCA)
> -- Oracle Database Programmer/Administrator
> -- Configuration Management Officer
> -- Microsoft Certified Professional
>
> * E-Mail: robert.yexley_at_afit.af.mil
> * Voice: DSN - 785-6565 x-4268
> Civ - (937) 255-6565 x-4268
> Fax: DSN - 986-7080
> Civ - (937) 656-7080
> * http://sc.afit.af.mil/sca
>
> <)))><
> _________________________
> "God who gave us life gave us liberty.
> Can the liberties of a nation be secure
> when we have removed a conviction
> that these liberties are a gift of God?
> Indeed I tremble for my country when
> I reflect that God is just, that his
> justice cannot sleep forever."
> - Thomas Jefferson, 3rd US President
> ______________________________
>
>
> --
> Author: Yexley Robert D SSgt AFIT/SCA
> INET: Robert.Yexley_at_afit.af.mil
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Fri Jul 21 2000 - 15:21:33 CDT