Home » SQL & PL/SQL » SQL & PL/SQL » need to merge the records
need to merge the records [message #247346] Mon, 25 June 2007 11:31 Go to next message
raj_vs_world
Messages: 4
Registered: June 2007
Junior Member
Hi,

I have a table like this :

create table cc
( start_time timestamp,
  end_time timestamp,
  distance  number,
  avg_speed  number
);


and the data resides in this table for a trip ie the details for a trip are stored in this table. now, i get a new record and based on the condition that :
new_record.start_time - old_record.end_time > 4 hrs
i want to update the old record with new information or insert in the other case..
plz suggest a way to do this..

Thanks in advance..
-neeraj
Re: need to merge the records [message #247368 is a reply to message #247346] Mon, 25 June 2007 12:35 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Use oracle merge statement if you are using 9i or greater.
some thing like this

 MERGE INTO bonuses B
USING (
  SELECT employee_id, salary
  FROM employee
  WHERE dept_no =20) E
ON (B.employee_id = E.employee_id)
WHEN MATCHED THEN
  UPDATE SET B.bonus = E.salary * 0.1
WHEN NOT MATCHED THEN
  INSERT (B.employee_id, B.bonus)
  VALUES (E.employee_id, E.salary * 0.05);
Re: need to merge the records [message #247370 is a reply to message #247346] Mon, 25 June 2007 12:40 Go to previous messageGo to next message
raj_vs_world
Messages: 4
Registered: June 2007
Junior Member
Thanks for the reply..
but i want to update old.end_time with new.start_time and then i cant use old.end_time in the comparison..(i stated)
plz provide a workaround..

Re: need to merge the records [message #247371 is a reply to message #247370] Mon, 25 June 2007 12:52 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
its with constraints...

 1  create table test1
  2  (sno number,
  3  old_date date
  4* )
SQL>
SQL>
SQL> /

Table created.

SQL> insert into test1
  2  values
  3  (1,sysdate);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1;

       SNO OLD_DATE
---------- -------------------
         1 06-25-2007 17:44:36
         1 06-25-2007 17:44:42
         1 06-25-2007 17:44:43

SQL> create table test2
  2  (sno number,
  3  new_date date );

Table created.

SQL> insert into test2
  2  values(2,sysdate);

1 row created.

SQL> insert into test2
  2  values
  3  (3,sysdate+1);

1 row created.

SQL> insert into test2
  2  values
  3  (1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test2;

       SNO NEW_DATE
---------- -------------------
         2 06-25-2007 17:45:45
         3 06-26-2007 17:46:09
         1 06-25-2007 17:46:40

SQL> merge into test1 T1
  2  using test2 T2
  3  on
  4  (t2.sno=t1.sno)
  5  when matched then update
  6  set
  7  t1.old_date=t2.new_date
  8  WHEN NOT MATCHED THEN
  9  insert (t1.sno,t1.old_date)
 10  values
 11  (t2.sno,t2.new_date);

5 rows merged.

SQL> select * from test1;

       SNO OLD_DATE
---------- -------------------
         1 06-25-2007 17:46:40
         1 06-25-2007 17:46:40
         1 06-25-2007 17:46:40
         2 06-25-2007 17:45:45
         3 06-26-2007 17:46:09

[Updated on: Mon, 25 June 2007 12:55]

Report message to a moderator

Re: need to merge the records [message #247375 is a reply to message #247346] Mon, 25 June 2007 13:19 Go to previous messageGo to next message
raj_vs_world
Messages: 4
Registered: June 2007
Junior Member
i tried the following query but didnt work :


CREATE TABLE DUMMY1 ( 
  NAME          VARCHAR2 (20), 
  START_TIME    DATE, 
  END_TIME      DATE, 
  DISTANCE      NUMBER, 
  CNT           NUMBER
);

INSERT INTO DUMMY1 ( NAME, START_TIME, END_TIME, DISTANCE, CNT) 
VALUES ( 'sahil',  TO_Date( '06/22/2007 09:10:00 PM', 'MM/DD/YYYY HH:MI:SS AM'),  TO_Date( '06/22/2007 10:45:00 PM', 'MM/DD/YYYY HH:MI:SS AM'), 45, 10);

INSERT INTO DUMMY1 ( NAME, START_TIME, END_TIME, DISTANCE, CNT) 
VALUES ( 'neeraj',  TO_Date( '06/21/2007 03:30:00 PM', 'MM/DD/YYYY HH:MI:SS AM'),  TO_Date( '06/22/2007 02:55:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 21, 5);


create table dummy2 as select * from dummy1 where 1=2

INSERT INTO DUMMY2 ( ENAME, START_TIME, END_TIME, DISTANCE, CNT ) VALUES ( 
'sahil',  TO_Date( '06/23/2007 03:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),  TO_Date( '06/23/2007 08:30:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 8, 2); 


merge into dummy1 d1
using(select * from dummy2 ) d2
on(d1.name=d2.ename and (d2.start_time-d1.end_time)<4/24)
when matched then
update set d1.end_time=d2.end_time,
	   d1.distance=d1.distance+d2.distance,
	   d1.cnt=d1.cnt+d2.cnt
when not matched then
insert(d1.name,d1.start_time,d1.end_time,d1.distance,d1.cnt)
values(d2.ename,d2.start_time,d2.end_time,d2.distance,d2.cnt)




now, this merge statement results in an error because i'm using the same column in the update and on clause..

as a workaround, i added a new column to dummy1:

alter table dummy1 add(end_time_dup date);



and used this new column in the on clause

but i have to fill in this column with the values from dummy1.end_time every time before executing this query..

am i correct in my approach or something else is possible..

Thanks
-neeraj

Re: need to merge the records [message #247384 is a reply to message #247375] Mon, 25 June 2007 14:14 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
merge into dummy1 d1
using(select * from dummy2 ) d2
on(d1.name=d2.ename and (d2.start_time-d1.end_time)<4/24)
when matched then
update set d1.end_time=d2.end_time,
	   d1.distance=d1.distance+d2.distance,
	   d1.cnt=d1.cnt+d2.cnt
when not matched then
insert(d1.name,d1.start_time,d1.end_time,d1.distance,d1.cnt)
values(d2.ename,d2.start_time,d2.end_time,d2.distance,d2.cnt)



try this one

merge into dummy1 d1
using(select Name,start_time,end_time,distance,cnt from dummy2 ) d2
on(d1.name=d2.ename and (d2.start_time-d1.end_time)<4/24)
when matched then
update set 
d1.start_time=d2.start_time
d1.end_time=d2.end_time,
d1.distance=d1.distance+d2.distance,
d1.cnt=d1.cnt+d2.cnt
when not matched then
insert(d1.start_time,d1.end_time,d1.distance,d1.cnt)
values(d2.start_time,d2.end_time,d2.distance,d2.cnt)



also post your errors.
Re: need to merge the records [message #247457 is a reply to message #247346] Mon, 25 June 2007 23:27 Go to previous message
raj_vs_world
Messages: 4
Registered: June 2007
Junior Member

tried.. didnt work..

SQL> merge into dummy1 d1
  2  using(select eName,start_time,end_time,distance,cnt from dummy2 ) d2
  3  on(d1.name=d2.ename and (d2.start_time-d1.end_time)<4/24)
  4  when matched then
  5  update set 
  6  d1.start_time=d2.start_time,
  7  d1.end_time=d2.end_time,
  8  d1.distance=d1.distance+d2.distance,
  9  d1.cnt=d1.cnt+d2.cnt
 10  when not matched then
 11  insert(d1.start_time,d1.end_time,d1.distance,d1.cnt)
 12  values(d2.start_time,d2.end_time,d2.distance,d2.cnt)
 13  /
on(d1.name=d2.ename and (d2.start_time-d1.end_time)<4/24)
                                       *
ERROR at line 3:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "D1"."END_TIME"



n what was the difference between the two..
n plz tell me about the workaround i mentioned.. is it ok or we can do without having the need to update an additional column each time before executing the query..

regards..
-neeraj

Previous Topic: Default values
Next Topic: Format problem........
Goto Forum:
  


Current Time: Tue Dec 06 08:43:03 CST 2016

Total time taken to generate the page: 0.11325 seconds