Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Mass updates from one table to another

RE: Mass updates from one table to another

From: Yexley Robert D SSgt AFIT/SCA <Robert.Yexley_at_afit.af.mil>
Date: Fri, 21 Jul 2000 17:15:31 -0400
Message-Id: <10565.112692@fatcity.com> 





Uummm.....ok, I feel silly now.  You were right, that did work, I just had
my syntax SLIGHTLY different from that, and it wasn't right.  Originally I
had the following:


UPDATE afitnet_user_name
SET pwd_expire_date =
	(SELECT d.pwd_expire_date
	 FROM	afitnet_user_name_details d, afitnet_user_name a
	 WHERE  d.ssan = a.ssan)





and it returned ORA-01427: single-row subquery returns more than one row



Then I changed it to this:



UPDATE afitnet_user_name


SET pwd_expire_date =

	(SELECT pwd_expire_date
	 FROM	afitnet_user_name_details
	 WHERE  ssan = afitnet_user_name.ssan)





and that worked just fine.  Sorry for the confusion I may have caused, and
thanks for the help.



-::YEX::-



<)))><




-----Original Message-----


From: Kirsh, Gary [mailto:gary.kirsh_at_gs.com]
Sent: Friday, July 21, 2000 17:23


To: Multiple recipients of list ORACLE-L
Subject: RE: Mass updates from one table to another




Sarge,


Assuming SSAN is unique, this should work, although PLSQL might be more
efficient.



update AFITNET_USER_NAME AUN


set    PWD_EXPIRE_DATE = 

         (select PWD_EXPIRE_DATE  
          from   AFITNET_USER_NAME_DETAILS AUND
          where  AUND.SSAN = AUN.SSAN)





HTH,



Gary



Gary Kirsh


Next Extent, Inc.



-----Original Message-----


Sent: Friday, July 21, 2000 4:22 PM


To: Multiple recipients of list ORACLE-L




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







<)))><





"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."



-- 
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
also send the HELP command for other information (like subscribing).
-- 
Author: Kirsh, Gary
  INET: gary.kirsh_at_gs.com

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


Received on Fri Jul 21 2000 - 16:15:31 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US