SQL更新，删除和同时插入 | 删除和同时插入

本文介绍了SQL更新，删除和同时插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我只是对某事感到好奇.让我说我有一个表，我将更新该值，然后将其删除，然后插入新的1.如果我以这种方式编写代码，这将非常容易:

I just curious about something. Let said i have a table which i will update the value, then deleted it and then insert a new 1. It will be pretty easy if i write the coding in such way:

  UPDATE PS_EMAIL_ADDRESSES SET PREF_EMAIL_FLAG='N' WHERE EMPLID IN ('K0G004');

  DELETE  FROM PS_EMAIL_ADDRESSES WHERE EMPLID='K0G004' AND E_ADDR_TYPE='BUSN';

  INSERT INTO PS_EMAIL_ADDRESSES VALUES('K0G004', 'BUSN', '[email protected]', 'Y');

但是，如果使用"update"语句，它将更加容易.但是我的问题是，是否有可能同时完成这3个步骤?

however, it will be much more easy if using 'update' statement. but My question was, it that possible that done this 3 step in the same time?

推荐答案

引用Oracle Transaction Statements文档:

此外，引用Wikipedia交易帖子:

原子性要求每一笔交易都是全有或全无" :如果有交易的一部分失败，整个交易失败，并且数据库状态保持不变.

Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.

以您的情况，您可以将所有三个句子括在一个交易中:

In your case, you can enclose all three sentences in a single transaction:

COMMIT;         ''This statement ends any existing transaction in the session.
SET TRANSACTION NAME 'my_crazy_update'; ''This statement begins a transaction
                                         ''and names it sal_update (optional).
 UPDATE PS_EMAIL_ADDRESSES
    SET PREF_EMAIL_FLAG='N'
  WHERE EMPLID IN ('K0G004');

 DELETE FROM PS_EMAIL_ADDRESSES
  WHERE EMPLID='K0G004' AND E_ADDR_TYPE='BUSN';

 INSERT INTO PS_EMAIL_ADDRESSES
 VALUES('K0G004', 'BUSN', '[email protected]', 'Y');

COMMIT;

这是满足您的要求的最佳方法一次完成所有句子" .

This is the best approach to catch your requirement 'do all sentences at a time'.

这篇关于SQL更新，删除和同时插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！