December 16, 2015 Oracle Statement implementation has update none insert

Before using the Oracle database, I was using a MySQL database, I know that the MySQL database implementation is updated, no update, there is a statement,

But Oracle's ability to do this is a moment of frustration for me, but I'm sure the Oracle database will provide such statements.

So...

1) Oracle implementation is updated, none inserted

Using the merge into statement

The following parameters are known: three UserID, name, and sex. Requires that the name and sex fields be updated if the same userid exists in the user table (T_user), and a record is inserted if the UserID is not present in the user table (T_user)

MERGE into T_user T1

USING (SELECT userid as id,name as username,sex as usersex from dual) T2

on (t1.userid=t2.id)

When matched then

Update Set T1.name=t2.username,t1.sex=t2.usersex

When isn't matched then

Insert (Userid,name,sex) VALUES (t2.id,t2.username,t2.usersex);

2) MySQL implementation is updated, none inserted

MySQL needs to implement the above functions, how to do it?

Using the on DUPLICATE KEY UPDATE statement

It is known that the parameters passed over three IDs, username, usersex their values are: 1 three men.

INSERT into T_user (userid,name,sex) VALUES (id,username,usersex) on DUPLICATE KEY UPDATE name=username,sex= Usersex;

Note: If you use this form in MySQL, you need the UserID field in the datasheet to be a primary key or to have a unique index

If you need to quote, please indicate the source: http://www.cnblogs.com/anzhao/p/5050555.html

December 16, 2015 Oracle Statement implementation has update none insert