Martijn,
Here is a quick test (Oracle 10.1.0.3/Linux):
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
SQL> select * from merge_test_1;
ID NAME
---------- --------------------
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
1 xxx
6 rows selected.
SQL> select * from merge_test_2;
ID NAME
---------- --------------------
1 AAA
2 BBB
6 FFF
SQL> select index_name from user_indexes where table_name like
'merge_test%';
no rows selected
SQL> merge into merge_test_1 a1
2 using merge_test_2 a2
3 on (a1.id = a2.id)
4 when matched then
5 update set a1.name = a2.name
6 when not matched then
7 insert (id, name) values (a2.id, a2.name);
4 rows merged.
SQL> select * from merge_test_1;
ID NAME
---------- --------------------
1 AAA
2 BBB
3 ccc
4 ddd
5 eee
1 AAA
6 FFF
7 rows selected.
Regards,
Lubomir Petrov
Martijn van Oosterhout wrote:
On Wed, Nov 23, 2005 at 12:24:24AM +0100, Petr Jelinek wrote:
Btw about that keys, oracle gives error on many-to-one or many-to-many
relationship between the source and target tables.
The standard has something called a "cardinality violation" if the
to-be-merged table doesn't match 1-1 with the rest of the statement. If
I had access to an Oracle I'd run two tests on MERGE:
1. Does the joining column have to have an index? For example, make a
column that's full of unique values but no unique index. According to
my reading of the the standard, this should still work (just slower).
2. Additionally, only the rows involved in the MERGE need to be
uniquely referenced, so if you add duplicate values but add a WHERE
clause to exclude those, it should also work.
My feeling is that requiring an index will limit it's usefulness as a
general tool.
Have a nice day,
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
