update using merge

H2o · #1 (**permalink**) 08-06-2007, 12:35 PM

Hello,

In Oracle ,I want to update flag = 'Y' for all records whose enddate is not null. So i can easily write a query update users set flag = 'Y' where enddate is not null.

how can i achieve this using merge statement. Since i have 1 million records, my update is taking huge time. I want to write this query using merge statement. Plz help

User id startDate enddate flag
1 10-jan-04 10-jan-04
1 11-mar-05 12-mar-05
1 11-mar-05

2 10-jan-04 10-jan-04
2 11-mar-05 12-mar-05
2 11-mar-05

etc etc
etc etc

priyan · #2 (**permalink**) 08-07-2007, 12:35 AM

Hi,

Here is the solution...

I created testing table with some testing records as per your requirement..

Quote:

CREATE TABLE merge_test(userid INTEGER, startDate DATE, endDate DATE, flag CHAR);

-- Insert some dummy records for testing...
INSERT INTO merge_test(userid, startDate, endDate) VALUES(1, to_date('10-Jan-04', 'dd/mon/yyyy'), to_date('10-Feb-07','dd/mon/yyyy'));
INSERT INTO merge_test(userid, startDate, endDate) VALUES(2, to_date('11-Mar-04', 'dd/mon/yyyy'), to_date('12-Mar-07','dd/mon/yyyy'));
INSERT INTO merge_test(userid, startDate) VALUES(4, to_date('20-Mar-04', 'dd/mon/yyyy'));
INSERT INTO merge_test(userid, startDate) VALUES(5, to_date('11-May-04', 'dd/mon/yyyy'));
INSERT INTO merge_test(userid, startDate, endDate) VALUES(2, to_date('15-Mar-04', 'dd/mon/yyyy'), to_date('20-Mar-07','dd/mon/yyyy'));

-- MERGE usage...

MERGE INTO merge_test t1
USING (SELECT userid,startDate, endDate, 'Y' Flag FROM merge_test WHERE enddate IS NULL) t2
ON (t1.userid = t2.userid)
WHEN MATCHED THEN
UPDATE SET t1.flag = 'Y'
WHEN NOT MATCHED THEN
INSERT (userid, startdate, enddate, flag) VALUES (null,null,null,null);

-- Here i just included INSERT statement also since oracle 9i needs both MATCHED and NOT MATCHED statements mandatory. But if you are using Oracle 10 then you may ignore WHEN NOT MATCHED clause in the merge, these are optional for Oracle 10.

Quote:

-- Now the result of the table would be
SELECT * FROM merge_test
-------------------------------------------------------
USERID STARTDATE ENDDATE FLAG
-------------------------------------------------------
1 1/10/0004 2/10/0007 {null}
2 3/11/0004 3/12/0007 {null}
4 3/20/0004 {null} Y
5 5/11/0004 {null} Y
2 3/15/0004 3/20/0007 {null}
-------------------------------------------------------

Just try this..

suresh · #3 (**permalink**) 08-20-2007, 07:03 AM

I'm having difficulty in working out why you think using merge over update will give you any benefits? As per MERGE Statement , merge is used to update or insert, whereas you appear to just need an update.

Update is the way to go...

prasannavigneshr · #4 (**permalink**) 08-20-2007, 07:16 AM

If i understand correctly your posting that you want to use a MERGE statement to improve the performance of your update. if you only to do a simple update then your update statement is correct. you will not need the MERGE statement unless you involve some other DML operations that you want to do at the same time.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
next Google update ?	Booom	Google	12	02-09-2008 08:59 PM
How to Merge Images nearby aside.	oxygen	C# Programming	3	10-26-2007 09:06 AM
Can I update two (or more) frames simultaneously?	GDevakii	HTML, CSS and Javascript Coding Techniques	1	10-26-2007 07:17 AM
Update the value back	leoraja8	Database Support	4	09-27-2007 04:21 AM
What happened last update?	Joe	Google	1	02-21-2007 01:22 AM