| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Mass updates from one table to another
update afitnet_user_name a1
set a1.PWD_EXPIRE_DATE =3D (select a2.PWD_EXPIRE_DATE from afitnet_user_det=
ails a2 where a1.ssan =3D a2.ssan)
This assumes that there is a one to one relationship, if there is not you = might try something like
update afitnet_user_name a1
set a1.PWD_EXPIRE_DATE =3D (select max(a2.PWD_EXPIRE_DATE) from afitnet_use=
r_details a2 where a1.ssan =3D a2.ssan)
>>> Yexley Robert D SSgt AFIT/SCA <Robert.Yexley_at_afit.af.mil> 07/21/00 =
04:21PM >>>
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
<)))><
--=20
Author: Yexley Robert D SSgt AFIT/SCA
INET: Robert.Yexley_at_afit.af.mil=20
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:15:58 CDT
 |
 |