Oracle SQL Tutorial -6 Update Data In The Oracle Database

Hi ,

In this article , I will explain topics such as updating data , deleting data from table and adding records to table .

ADDING RECORD TO TABLE

The INSERT command is used when adding a new record to the table . To add a single record :

INSERT INTO table [(column [ ,column ...] VALUES (value [ , value ...]);

INSERT INTO ADMIN.ISCI(EMPLOYEE_ID,FIRST_NAME,LAST_NAME) VALUES (6,'Melike','Duran');

ROW COPYING FROM ANOTHER TABLE

• We use subqueries when copying rows from another table . The VALUES statement is not used .

• The number of columns in the INSERT statement with the subquery must be equal .

• All records from the subquery are INSERT .

INSERT INTO NEW_TABLE SELECT * FROM ISCI WHERE EMPLOYEE_ID=6;

UPDATING DATA IN THE TABLE

• The UPDATE statement is used to update any data in the table . This statement can update multiple records at once .

INSERT INTO ADMIN.ISCI (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY) VALUES (12,'Muzaffer','Kazan',1500);

In the query above , we added a new record to our table and entered the salary as 1500 . I’m going to update the salary data now .

UPDATE ADMIN.ISCI SET SALARY=SALARY+500 WHERE EMPLOYEE_ID=12;

And when we run the above query , we just changed the salary value of the person we added to the table from 1500 to 2000 .

Let’s make another example :

UPDATE OGRENCI SET BOLUM_ADI='Bilgisayar Mühendisliği' WHERE BOLUM_ADI='Tarih';

This example changed a single field like our first example and now we will update two fields in a single query .

UPDATE OGRENCI SET AD='Aylin' , BOLUM_ADI='Gastronomi' WHERE OGRENCI_NO=17700;

In our student table , we will change both the name and the section name of the person whose ogrenci_no is 17700 .

DELETING DATA FROM THE TABLE

The DELETE command is used when we want to delete data from the table . With this command , we can delete more than one record at a time .

TRUNCATE : Used to delete all data in the table and is irreversible .

TRUNCATE is a DDL command .

Now let’s look at the values in our table .

Our table looked like this after we just made an update , and now let’s delete Deniz from the table .

DELETE FROM ADMIN.ISCI WHERE FIRST_NAME='deniz';

Now let’s give an example of truncate .

CREATE TABLE ADMIN.YENI_TABLO AS SELECT * FROM ADMIN.ISCI;

First of all , we created a table named new_table by copying the ıscı table . Then we delete this table with the truncate statement .

TRUNCATE TABLE ADMIN.YENI_TABLO;

And now let’s check to see if our table has been deleted .

SELECT * FROM ADMIN.YENI_TABLO;

This way we received a warning , so our table was completely deleted .

DATABASE TRANSACTIONS

• When we run a DML command , transaction starts . The transaction terminates in the following cases :

• When a DDL or DCL command is executed ,

• When the user has a logout ,

• If the system fails ,

• When Commit or Rollback commands are executed

The transaction ends .

COMMIT & ROLLBACK

• ROLLBACK : Undo changes to data .

• COMMIT : Ensures that data changes are permanent .

• These commands help you track data changes before you apply continuous changes.

• The changes made are controlled on a session basis .

SAVEPOINT

• Used to put a record point in the current transaction .

BEFORE COMMIT OR ROLLBACK

• The transaction user will see the result of the change he made after SELECT and DML operations .

• Other users cannot see the result of the DML operations performed by the user doing the operation .

• The affected rows are locked , and other users cannot change the data on unaffected rows .

AFTER COMMIT

• Data changes made in the database are applied permanently .

• All users can see the current result .

• The affected rows are unlocked .

• All SAVEPOINT points are deleted .

• **When deleting a record from a table , if the transaction is not committed , the user can see the change but the process is not permanent and the other users cannot see the result of the transaction .

DELETE FROM ADMIN.ISCI WHERE EMPLOYEE_ID=4;

SELECT * FROM ADMIN.ISCI;

We deleted the person with id above 4 , but this process did not happen permanently and now we commit it to make it permanent :

COMMIT;

SELECT * FROM ADMIN.ISCI;

AFTER ROLLBACK

• Changes made to the data are undone .

• The records that are modified are released , the locks are removed .

• When uncommitted transaction Rollback is performed , unmodified data is retrieved from the Undo tablespace and the data becomes consistent .

DELETE FROM ADMIN.ISCI;

ROLLBACK;

Now we’re checking to see if it’s taken back :

SELECT * FROM ADMIN.ISCI;

FOR UPDATE

• Locks selected records to prevent other users from changing them .

• Records are released when Rollback or Commit is performed .

• If more than one table is included in the join , all the selected records are locked in both tables .

• A user pulls the table with the for update option , and when a different user tries to delete the table , this operation is not allowed , it falls into the corresponding session lock .

See you in my next post.