Home » SQL & PL/SQL » SQL & PL/SQL » update table columns depending on other table column values (Oracle 9i , 9.2.0.1.0 , Linux)
update table columns depending on other table column values [message #316247] Thu, 24 April 2008 06:53 Go to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Hi
i have 2 tables gtpass1 and gtpass2 . i want to update
records of table gtpass2 from gtpass1 by matching EMPNO,DATE and closest time in TIME1 column from both table.
Test case-
Table creation script-
CREATE TABLE GATEPASS1
(
  EMPNO     VARCHAR2(5) NOT NULL,
  TIME1     DATE,
  TIME2     DATE,
  PEROFF    VARCHAR2(3),
  GTPASSNO  NUMBER(10)
);

INSERT INTO GATEPASS1 ( EMPNO, TIME1, TIME2, PEROFF, GTPASSNO ) VALUES ( 
'10367',  
TO_Date( '04/22/2008 09:17:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),  T
O_Date( '04/22/2008 11:17:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 'P', 1); 
INSERT INTO GATEPASS1 ( EMPNO, TIME1, TIME2, PEROFF, GTPASSNO ) VALUES ( 
'10367',  
TO_Date( '04/22/2008 01:17:00 PM', 'MM/DD/YYYY HH:MI:SS AM'),  
TO_Date( '04/22/2008 03:30:00 PM', 'MM/DD/YYYY HH:MI:SS AM')
, 'P', 2); 
INSERT INTO GATEPASS1 ( EMPNO, TIME1, TIME2, PEROFF, GTPASSNO ) VALUES ( 
'10367', 
 TO_Date( '04/22/2008 04:15:00 PM', 'MM/DD/YYYY HH:MI:SS AM'),  
TO_Date( '04/22/2008 04:30:00 PM', 'MM/DD/YYYY HH:MI:SS AM')
, 'O', 3); 
commit;
 

CREATE TABLE GATEPASS2
(
  EMPNO     VARCHAR2(5) NOT NULL,
  TIME1     DATE,
  TIME2     DATE,
  PEROFF    VARCHAR2(3),
  GTPASSNO  NUMBER(10)
);

INSERT INTO GATEPASS2 ( EMPNO, TIME1, TIME2, PEROFF, GTPASSNO ) VALUES ( 
'10367',  
TO_Date( '04/22/2008 09:20:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),  
TO_Date( '04/22/2008 11:19:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, NULL, NULL); 
INSERT INTO GATEPASS2 ( EMPNO, TIME1, TIME2, PEROFF, GTPASSNO ) VALUES ( 
'10367',  
TO_Date( '04/22/2008 01:22:00 PM', 'MM/DD/YYYY HH:MI:SS AM'),  
TO_Date( '04/22/2008 03:31:00 PM', 'MM/DD/YYYY HH:MI:SS AM')
, NULL, NULL); 
INSERT INTO GATEPASS2 ( EMPNO, TIME1, TIME2, PEROFF, GTPASSNO ) VALUES ( 
'10367',  
TO_Date( '04/22/2008 04:18:00 PM', 'MM/DD/YYYY HH:MI:SS AM'),  
TO_Date( '04/22/2008 04:30:00 PM', 'MM/DD/YYYY HH:MI:SS AM')
, NULL, NULL); 
commit;


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for Linux: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> SELECT EMPNO,TO_CHAR(TIME1,'DD/MM/YYYY HH24:MI') TIME1,
  2         TO_CHAR(TIME2,'DD/MM/YYYY HH24:MI') TIME2,PEROFF,GTPASSNO
  3  FROM   gatepass1;

EMPNO TIME1            TIME2            PER   GTPASSNO
----- ---------------- ---------------- --- ----------
10367 22/04/2008 09:17 22/04/2008 11:17 P            1
10367 22/04/2008 13:17 22/04/2008 15:30 P            2
10367 22/04/2008 16:15 22/04/2008 16:30 O            3



SQL> SELECT EMPNO,TO_CHAR(TIME1,'DD/MM/YYYY HH24:MI') TIME1,
  2         TO_CHAR(TIME2,'DD/MM/YYYY HH24:MI') TIME2,PEROFF,GTPASSNO
  3  FROM   gatepass2;

EMPNO TIME1            TIME2            PER   GTPASSNO
----- ---------------- ---------------- --- ----------
10367 22/04/2008 09:20 22/04/2008 11:19
10367 22/04/2008 13:22 22/04/2008 15:31
10367 22/04/2008 16:18 22/04/2008 16:30


Now i want to update peroff and gtpassno column of gtpass2 table
by matching closest value of time1 column from gtpass1 table.
so for first record
10367 22/04/2008 09:20 22/04/2008 11:19 

peroff should get updated to 'P' and gtpassno = 1.
and so on.
Right now we are doing this in pl/sql block using cursors.
Is this can be done in single SQL ?
Any suggestions / hints ?
thanks in advance.


[Updated on: Thu, 24 April 2008 07:01] by Moderator

Report message to a moderator

Re: update table columns depending on other table column values [message #316254 is a reply to message #316247] Thu, 24 April 2008 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use MERGE.

Regards
Michel
Re: update table columns depending on other table column values [message #316437 is a reply to message #316254] Fri, 25 April 2008 02:26 Go to previous message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Thank you for quick reply.
I am trying it with Merge.
Previous Topic: [ ORA-01031: insufficient privileges ] error while creating trigger
Next Topic: pl/sql code for a VIEW
Goto Forum:
  


Current Time: Sun Dec 11 00:48:52 CST 2016

Total time taken to generate the page: 0.08178 seconds