SQL Update,Delete And Insert In Same Time

Question

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', 'ABS@GNC.COM.BZ', 'Y');

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?

If you're going to `DELETE` a row and then `INSERT` a row that is, effectively, the "same" as the row you just deleted, why not just `UPDATE` the existing row? It's not clear (to me) why you're trying to perform all 3 operations at the same time — Damien_The_Unbeliever, Jul 15 '13 at 06:29
Row seems back just modified, no need to run all three, just do update and make it as desired — mirkobrankovic, Jul 15 '13 at 06:31
@Damien_The_Unbeliever If it one row yes. The second statement may be deleting more than one (or none at all) rows though. — ypercubeᵀᴹ, Jul 15 '13 at 06:32
yup~update is the more better way. And i prefer use such way as well. But i just curious about is that possible to done this 3 step in the same time? — goh6319, Jul 15 '13 at 06:53
You talk about 'the same time', it likes you are talking abut [transactions](http://msdn.microsoft.com/en-us/library/ms174377.aspx). Tansactions have ACID property where A is for Atomicity. That means, all sentences are executed as a single operation. Are you looking for that? — dani herrera, Jul 15 '13 at 09:42
@goh6319, [doed](http://stackoverflow.com/a/17676903/842935) — dani herrera, Jul 16 '13 at 12:46
Note that using a transaction is not the same as using a single statement. Transactions (in other sessions) might commit changes to the tables you use, so your first statement will "see" state 1 (the another session commits something) and your second statement will "see" state 2. You can avoid this by using transaction serialization or locking the table (both are bad for performance). Or using a single statement (that would be _MERGE_). — David Balažic, Jun 07 '16 at 21:53

dani herrera · Accepted Answer · 2013-07-16T12:53:50.320

Quoting Oracle Transaction Statements documentation:

A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID.

Also, quoting wikipedia Transaction post:

In computer science, ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

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', 'ABS@GNC.COM.BZ', 'Y');

COMMIT;

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

Hi danihp very impressive by the transaction function. Can i perform a simple checking on which part is fail? Because the transaction function will stop executing when the pass in parameter didn't meet the parameter in query. — goh6319, Jul 18 '13 at 00:39

score 0 · Answer 2 · answered Jul 15 '13 at 06:53

0

Use this UPDATE:

UPDATE PS_EMAIL_ADDRESSES
SET
PREF_EMAIL_FLAG = 'N',
E_ADDR_TYPE = 'BUSN',
`column1_name` = 'ABS@SEMBMARINE.COM.SG',
`column2_name` = 'Y'
WHERE EMPLID = 'K0G004';

Where column1_name and column2_name are the column names that you use for those values.

answered Jul 15 '13 at 06:53

Gimmy

3,781
2
18
27

Hi Gimmy thank you for your suggestion, It that possible if i done this 3 step in 1 execution only? :D – goh6319 Jul 15 '13 at 06:56
1

If you realy want to do 3 operations in one go you can try something like in this question: http://stackoverflow.com/questions/14259249/mysql-insert-or-update-if Though, by using my update you are making the changes that you wanted. – Gimmy Jul 15 '13 at 07:03
yup~i knew using update statement can done it quickly easily, i just curious about is that possible to execute 3 different step in 1 execution only. – goh6319 Jul 15 '13 at 07:07

LoztInSpace · Answer 3 · 2013-07-15T08:07:56.867

-1

Write a stored procedure to do all the operations you want and call that. That would be a single statement!

edited Jul 15 '13 at 08:07

answered Jul 15 '13 at 07:52

LoztInSpace

5,584
1
15
27

Would you mind to explain in more details how perform the merge statement by update,deleted and then insert at the same time? – goh6319 Jul 15 '13 at 08:07
I just removed that part. You could do it but it would be very contrived. I've edited it to suggest you just write a procedure. – LoztInSpace Jul 15 '13 at 08:09

SQL Update,Delete And Insert In Same Time

3 Answers3

Linked