AD

oracle merge in a simple to use

merge in oracle from another table to take the data into a table, two tables in the record if certain conditions are met, then replace, if you do not comply with the insert. The following example is recorded in the mergetest updated to newmergetest, if the two tables the same id, then update newmergetest, or insert:

First we construct two test table:

 create table mergetest(id number primary key,name varchar(20));
 create table newmergetest(id number primary key,name varchar(20));

Insert the data into two tables:

insert into newmergetest values(1,'first');
insert into newmergetest values(2,'second');

insert into mergetest values(1,'one')
insert into mergetest values(2,'two');
insert into mergetest values(3,'three');


Run the update sql statement:

MERGE INTO newmergetest m 
using (select * from mergetest) t   
ON (m.id = t.id) 
WHEN NOT MATCHED THEN 
INSERT VALUES (t.id,t.name)   
WHEN MATCHED THEN
UPDATE SET m.name = t.name

After implementation, newmergetest the records will be and mergetest in agreement. Will not ** the primary key constraint and the update failed.
标签: implementation, oracle, two tables, constraint, test table
分类: Database
时间: 2010-09-05

相关文章

  1. Oracle Merge statement Efficiency

    You will come across some database operations in the update, Also considered the primary key will repeat the q ...
  2. Oracle Merge statement in the efficiency of

    You will come across some database operations in the update, Also considered the primary key will repeat the q ...
  3. New to oracle Here are some simple configuration, hoping to be helpful for beginners, the boss who can Tidianyixian

    New to oracle Here are some simple configuration, hoping to be helpful for beginners, the boss who can Tidiany ...
  4. New to oracle Here are some simple configuration, hoping for a little help for beginners, the boss who can Tidianyixian

    New to oracle Here are some simple configuration, hoping for a little help for beginners, the boss who can Tid ...
  5. Oracle lock table problem simple processing techniques

    Oracle lock table problem simple processing techniques http://database.51cto.com 2010-04-16 14:27 Anonymous I ...
  6. Oracle MERGE INTO的用法示例介绍

    这篇文章主要介绍了Oracle MERGE INTO的用法,需要的朋友可以参考下 很多时候我们会出现如下情境,如果一条数据在表中已经存在,对其做update,如果不存在,将新的数据插入.如果不使用Oracle提供的mer ...
  7. Oracle merge multiple rows / connect / polymer string in several ways (change)

    Merge multi-line records how the string has been oracle SQL novice like to ask one of the issues post on this ...
  8. Oracle merge line on a small record (transfer)

    Transfer from: http://www.ll19.com/log/2010/06/04/115.html ORACLE encountered in a working merge line, sort th ...
  9. oracle merge in the transfer function

    oracle's merge functions such as: table1 (col_1a, col_1b, col_1c) table2 (col_2a, col_2b, col_2c) I would like ...
  10. Oracle backup and recovery simple batch file - change

    Recommendation one: Using Task Scheduler, batch files, and the EXP ORACLE export feature, can date back up fil ...
  11. oracle paging code (super simple wording)

    <% @ Page language = "java" contentType = "text / html; charset = utf-8" pageEncoding = ...
  12. oracle - merge usage (transfer)

    Oracle9i introduces the MERGE command, you can in a SQL statement on a table while the implementation of inser ...
  13. ORACLE database logical backup simple EXP / IMP (reprint)

    Reprinted from: http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4718366.aspx 1. The advantages of a log ...
  14. Efficiency in Oracle Merge statement

    We encountered some database operations will be in the update, Must also be considered the primary key duplica ...
  15. ORACLE database logical backup simple EXP / IMP (reproduced)

    Reprinted from: http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4718366.aspx 1. The advantages of a log ...
  16. oracle basic knowledge and simple functions

    oracle Administrator : sys========>admin system=====>manager scott======>tiger ====================== ...
  17. Oracle Merge into details

    The MERGE statement is a new Oracle9i syntax, UPDATE and INSERT statements to the merger. Through the MERGE st ...
  18. oracle merge into use

    Uses: merge command can be used with a table to modify or insert data into another table, insert or modify the ...
  19. Operating on Oracle Merge

    Hesitation project involves analysis of large data import, data each time before going into a database to dete ...