OraFAQ Forum: SQL & PL/SQL » need to merge the records

Home » SQL & PL/SQL » SQL & PL/SQL » need to merge the records

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

need to merge the records [message #247346]

Mon, 25 June 2007 11:31

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

Report message to a moderator

Re: need to merge the records [message #247368 is a reply to message #247346]

Mon, 25 June 2007 12:35

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);

Report message to a moderator

Re: need to merge the records [message #247370 is a reply to message #247346]

Mon, 25 June 2007 12:40

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

Report message to a moderator

Re: need to merge the records [message #247371 is a reply to message #247370]

Mon, 25 June 2007 12:52

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

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

Report message to a moderator

Re: need to merge the records [message #247384 is a reply to message #247375]

Mon, 25 June 2007 14:14

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.

Report message to a moderator

Re: need to merge the records [message #247457 is a reply to message #247346]

Mon, 25 June 2007 23:27

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 17:06:57 CST 2024