CODEWITHSUNDEEP
sqloracle-database
goh6319
goh6319

Reputation: 137

SQL Update,Delete And Insert In Same Time

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');

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?

Upvotes: 2

Views: 13830

Answers (3)

dani herrera
dani herrera

Reputation: 51675

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', '[email protected]', 'Y');

COMMIT;

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

Upvotes: 3

LoztInSpace
LoztInSpace

Reputation: 5697

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

Upvotes: -1

Gimmy
Gimmy

Reputation: 3911

Use this UPDATE:

UPDATE PS_EMAIL_ADDRESSES
SET
PREF_EMAIL_FLAG = 'N',
E_ADDR_TYPE = 'BUSN',
`column1_name` = '[email protected]',
`column2_name` = 'Y'
WHERE EMPLID = 'K0G004';

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

Upvotes: 0

Related Questions