Oracle数据库及神通数据库实现数据库数据有则更新或者跳过,没有则添加进数据库

Oracle数据库及神通数据库实现数据库数据有则更新或者跳过,没有则添加进数据库

SQL如下:

merge into basis_file_type t1
        USING (SELECT #{aaa} AS a
               from dual) t2
        on (t1.BASIS_TYPE_NAME = t2.a )
        when matched then
  			update set t1.ASSETS_BANKACCOUNTS_ID_CARD =  '1'
        when not matched then
            insert (id, BASIS_TYPE_NAME)
            VALUES (12312311222312001112121, '123123213211')

basis_file_type是表名 #{aaa} 代表的是数据你要查询的唯一标识 ,如果数据库存在这条数据则更新这条数据,如果不存在则添加。
当然也可以不更新

merge into basis_file_type t1
        USING (SELECT #{aaa} AS a
               from dual) t2
        on (t1.BASIS_TYPE_NAME = t2.a )
        when not matched then
            insert (id, BASIS_TYPE_NAME)
            VALUES (12312311222312001112121, '123123213211')

去掉更新的代码就忽略更新的操作,只判断存不存在,如果不存在就添加数据,存在就跳过

XML代码如下:

   merge into basis_file_type t1
        USING (SELECT #{assetsBankaccountsCardNumber} AS a
               from dual) t2
        on (t1.ASSETS_BANKACCOUNTS_CARD_NUMBER = t2.a )
        when not matched then
            insert (
                ID,
                USER_GROWTH_PERSONID,
                ASSETS_BANKACCOUNTS_PERSON_NAME,
                ASSETS_BANKACCOUNTS_ID_CARD,
                ASSETS_BANKACCOUNTS_CARD_NUMBER,
                ASSETS_BANKACCOUNTS_VOUCHER_CARD,
                REMARK
            )
            VALUES (
                #{id},
                #{userGrowthPersonid},
                #{assetsBankaccountsPersonName},
                #{assetsBankaccountsIdCard},
                #{assetsBankaccountsCardNumber},
                #{assetsBankaccountsVoucherCard},
                #{remark}
            )
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页