Message-Id: <10565.112683@fatcity.com> From: Shawn Ferris Date: Fri, 21 Jul 2000 14:21:33 -0600 Subject: 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@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@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@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@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