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_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:31:58 CDT