Message-Id: <10565.112686@fatcity.com> From: Regina Harter Date: Fri, 21 Jul 2000 13:31:58 -0700 Subject: Re: Mass updates from one table to another Well, assuming SSAN is unique in the AFITNET_USER_NAME_DETAILS table, have you tried something like this... update AFITNET_USER_NAME set PWDEXP_CODE = (select PWDEXP_CODE from AFITNET_USER_NAME_DETAILS where AFITNET_USER_NAME_DETAILS.PWDEXP_CODE = AFITNET_USER_NAME.PWDEXP_CODE) At 12:21 PM 7/21/00 -0800, you wrote: >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