Home » SQL & PL/SQL » SQL & PL/SQL » Convert update sql into merge (10g)
Convert update sql into merge [message #394592] Sat, 28 March 2009 02:11 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Im not sure why the why when i updated the rows using update statement, it was showing 3 rows updated but its only 2 actually, but merge was showing 2, its quite a misleading piece of information


SQL>  create table test1 (
  2   col1 number,
  3   col2 varchar2(10));

Table created.

SQL>  create table test2 (
  2   col1 number,
  3   col2 varchar2(10))  ;

Table created.

SQL>  insert into test1(col1) values (1);

1 row created.

SQL>  insert into test1(col1) values (2);

1 row created.

SQL>  insert into test1(col1) values (3);

1 row created.


SQL>  insert into test2(col1,col2) values (1,'one');

1 row created.

SQL> insert into test2(col1,col2) values (2,'two');

1 row created.

                 
SQL> commit;

Commit complete.

SQL> update test1 a 
  2  set col2 = (select col2 from test2 b where a.col1 = b.col1) ;

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * from test1;

      COL1 COL2
---------- ----------
         1 one
         2 two
         3


SQL> update test1 set col2 = null; -- just to reset

3 rows updated.

SQL> commit;

Commit complete.

SQL>  merge into test1 a
  2  using
  3   (select * from test2)b
  4  on (a.col1 = b.col1)
  5  when matched then
  6     update
  7        set a.col2 = b.col2;

2 rows merged.

SQL> commit;

Commit complete.

SQL> select * from test1;

      COL1 COL2
---------- ----------
         1 one
         2 two
         3


SQL> 

[Updated on: Sat, 28 March 2009 04:03]

Report message to a moderator

Re: Convert update sql into merge [message #394595 is a reply to message #394592] Sat, 28 March 2009 04:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The reason is obvious: since you have no restraining clause (= WHERE) on your update, all rows were updated. It just so happened that the row with COL1=3 got COL2 updated to null.
Re: Convert update sql into merge [message #394598 is a reply to message #394595] Sat, 28 March 2009 05:56 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Appreciate if you could elaborate more. If i understand correctly, i have where clause

where a.col1 = b.col1


I also did not specify any constraints on merge statement, but it shows the right number of merge records.
Re: Convert update sql into merge [message #394611 is a reply to message #394598] Sat, 28 March 2009 09:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You may have a where clause in the query that selects the NEW value for COL2, but you have no where clause on the set of records to be updated.
In the merge, the non-matching row fails the WHEN MATCHED clause, hence is not touched.
Re: Convert update sql into merge [message #394621 is a reply to message #394611] Sat, 28 March 2009 12:10 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
I think i understand now

update test1 a 
    set col2 = (select col2 from test2 b where a.col1 = b.col1
  and a.col2 is not null) ;


Returns 3 records updated


update test1 a 
    set col2 = (select col2 from test2 b where a.col1 = b.col1
  ) 
where a.col2 is not null


Returns 2 records updated.

To me both queries does the same job. But as you mentioned, probably just that the second query works by filtering only those record that needs to be updated. Tq for explanation

[Updated on: Sat, 28 March 2009 12:34]

Report message to a moderator

Re: Convert update sql into merge [message #394622 is a reply to message #394621] Sat, 28 March 2009 12:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Same problem.
You do not limit the records that need to be updated.

Compare this to what you try:
update table1 t1
set    col1 = (select whateveryouwant
               from   anothertable t2
               where  t2.colx = t1.coly
              )
where  exists (select 1
               from   anothertable t2
               where  t2.colx = t1.coly
              )


Now, the way to read this is:
update table1
update which rows? Answer: rows that comply to the clause "where exists...."
update to what? well, update to (select whateveryouwant)

Your queries lack a "update which rows?" limiter.


Hope I made it clearer now..

[Updated on: Sat, 28 March 2009 12:37]

Report message to a moderator

Re: Convert update sql into merge [message #394629 is a reply to message #394622] Sat, 28 March 2009 20:34 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Tq Frank for the example, i got it now

[Updated on: Sat, 28 March 2009 20:36]

Report message to a moderator

Re: Convert update sql into merge [message #394792 is a reply to message #394629] Mon, 30 March 2009 06:18 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you do this:
UPDATE test1 SET col2='Fish';
and then e-run your original update, you will see exactly why it says that 3 rows are being updated.
Previous Topic: Same Conect By query works fine in 9i but not in 10g - Please help
Next Topic: where the Select statement output will be stored
Goto Forum:
  


Current Time: Fri Dec 09 02:20:22 CST 2016

Total time taken to generate the page: 0.08439 seconds