oracle的merge into用法（存在即更新，否则插入）

oracle

        MERGE INTO tablename T1
        USING (
        <foreach collection="list" item="item" index="index" separator="union">
            select
            #{item.id} id,
            #{item.othersid} othersid,
            #{item.createtime} createtime,
            #{item.usage} usage,
            #{item.kind} kind,
            #{item.ckid} ckid
            from dual
        </foreach>
        ) T2 ON (T1.ckid = T2.ckid and
        T1.othersid = T2.othersid)
        WHEN MATCHED THEN
        UPDATE
        SET T1.usage = T2.usage
        WHEN NOT MATCHED THEN
        INSERT
        (id,
        othersid,
        createtime,
        usage,
        kind,
        ckid
        )
        VALUES
        (T2.id,
        T2.othersid,
        T2.createtime,
        T2.usage,
        T2.kind,
        T2.ckid
        )

这边呢要注意，foreach中的查询内容取个别名，还有就是逗号要注意，很容易报错，报错显示缺失等于号

据说merge into 效率更高些，具体我也没查

我的例子是批量插入，单个的按照语法copy就行了

语法

MERGE INTO  [your table-name] [rename your table here]

USING  ( [write your query here] )[rename your query-sql and using just like a table]

ON  ([conditional expression here] AND [...]...)

WHEN MATHED THEN [here you can execute some update sql or something else ]

WHEN NOT MATHED THEN [execute something else here ! ]

sql server
以后补充