{"id":11873,"date":"2019-11-08T19:23:32","date_gmt":"2019-11-08T19:23:32","guid":{"rendered":"https:\/\/ittutorial.org\/?p=11873"},"modified":"2019-11-18T00:05:16","modified_gmt":"2019-11-18T00:05:16","slug":"changing-data","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/changing-data\/","title":{"rendered":"Oracle SQL Tutorial -6 Update Data In The Oracle Database"},"content":{"rendered":"

Hi ,<\/span><\/p>\n

In this article , I will explain topics such as updating data , deleting data from table and adding records to table .<\/span><\/p>\n

ADDING RECORD TO TABLE<\/span><\/span><\/span><\/span><\/p>\n

The INSERT command is used when adding a new record to the table . To add a single record :<\/span> <\/span><\/span><\/p>\n

INSERT INTO table [(column [ ,column ...] VALUES (value [ , value ...]);\r\n<\/i><\/span><\/span><\/pre>\n
INSERT INTO ADMIN.ISCI(EMPLOYEE_ID,FIRST_NAME,LAST_NAME) VALUES (6,'Melike','Duran');<\/em><\/pre>\n
 \"\" <\/i><\/span><\/span><\/p>\n
ROW COPYING FROM ANOTHER TABLE<\/span><\/span><\/span><\/span><\/p>\n
    \n
  • \n
    We use subqueries when copying rows from another table . The VALUES statement is not used .<\/span> <\/span><\/span><\/p>\n<\/li>\n
  • \n
    The number of columns in the INSERT statement with the subquery must be equal .<\/span> <\/span><\/span><\/p>\n<\/li>\n
  • \n
    All records from the subquery are INSERT .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
    INSERT INTO NEW_TABLE SELECT * FROM ISCI WHERE EMPLOYEE_ID=6;<\/em><\/pre>\n
    \"\"<\/p>\n
    UPDATING DATA IN THE TABLE<\/span><\/span><\/span><\/span><\/p>\n
      \n
    • \n
      The UPDATE statement is used to update any data in the table . This statement can update multiple records at once .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
      INSERT INTO ADMIN.ISCI (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY) VALUES (12,'Muzaffer','Kazan',1500);<\/em><\/pre>\n
      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 .<\/span> <\/span><\/span><\/p>\n
      UPDATE ADMIN.ISCI SET SALARY=SALARY+500 WHERE EMPLOYEE_ID=12;<\/em><\/pre>\n
      \"\"<\/p>\n
      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 .<\/span> <\/span><\/span><\/p>\n
      Let’s make another example :<\/span><\/span><\/span><\/p>\n
      UPDATE OGRENCI SET BOLUM_ADI='Bilgisayar M\u00fchendisli\u011fi' WHERE BOLUM_ADI='Tarih';<\/em><\/pre>\n
      \"\"<\/p>\n
      This example changed a single field like our first example and now we will update two fields in a single query .<\/span><\/span><\/span><\/span><\/p>\n
      \"\"<\/p>\n
      UPDATE OGRENCI SET AD='Aylin' , BOLUM_ADI='Gastronomi' WHERE OGRENCI_NO=17700;<\/em><\/pre>\n
      In our student table , we will change both the name and the section name of the person whose ogrenci_no is 17700 . <\/span><\/span><\/p>\n
      \"\"<\/p>\n
      DELETING DATA FROM THE TABLE<\/span> <\/span><\/span><\/p>\n
      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 .<\/span> <\/span><\/span><\/p>\n
      TRUNCATE : <\/span><\/span>Used to delete all data in the table and is irreversible<\/span><\/span> .<\/span><\/span><\/p>\n
      TRUNCATE is a DDL command .<\/span><\/span><\/span><\/p>\n
      Now let’s look at the values in our table .<\/span> <\/span><\/span><\/p>\n
      \"\"<\/p>\n
      Our table looked like this after we just made an update , and now let’s delete Deniz from the table .<\/span> <\/span><\/span><\/p>\n
      DELETE FROM ADMIN.ISCI WHERE FIRST_NAME='deniz';<\/em><\/pre>\n
      \"\"<\/p>\n
      Now let’s give an example of truncate .<\/span> <\/span><\/span><\/p>\n
      CREATE TABLE ADMIN.YENI_TABLO AS SELECT * FROM ADMIN.ISCI;<\/em><\/pre>\n
      First of all , we created a table named new_table by copying the <\/span><\/span><\/span>\u0131sc\u0131<\/span><\/span><\/span> table .<\/span><\/span><\/span> Then we delete this table with the truncate statement .<\/span><\/p>\n
      TRUNCATE TABLE ADMIN.YENI_TABLO;<\/em><\/pre>\n
      And now let’s check to see if our table has been deleted .<\/span> <\/span><\/span><\/p>\n
      SELECT * FROM ADMIN.YENI_TABLO;<\/em><\/pre>\n
      \"\"<\/p>\n
      This way we received a warning , so our table was completely deleted .<\/span> <\/span><\/span><\/p>\n
      DATABASE TRANSACTIONS<\/span><\/span><\/p>\n
        \n
      • \n
        When we run a DML command , transaction starts . The transaction terminates in the following cases :<\/span> <\/span><\/span><\/p>\n<\/li>\n
      • \n
        When a DDL or DCL command is executed ,<\/span> <\/span><\/span><\/p>\n<\/li>\n
      • \n
        When the user has a logout ,<\/span> <\/span><\/span><\/p>\n<\/li>\n
      • \n
        If the system fails ,<\/span> <\/span><\/span><\/p>\n<\/li>\n
      • \n
        When Commit or Rollback commands are executed<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
        The transaction ends .<\/span> <\/span><\/span><\/p>\n
        COMMIT & ROLLBACK<\/span><\/span><\/p>\n
          \n
        • \n
          ROLLBACK : <\/span><\/span>Undo changes to data<\/span> .<\/span><\/span><\/p>\n<\/li>\n
        • \n
          COMMIT : <\/span><\/span>Ensures that data changes are permanent<\/span> .<\/span><\/span><\/p>\n<\/li>\n
        • \n
          These commands help you track data changes before you apply continuous changes.<\/span> <\/span><\/span><\/p>\n<\/li>\n
        • \n
          The changes made are controlled on a session basis .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
          SAVEPOINT<\/span><\/span><\/p>\n
            \n
          • \n
            Used to put a record point in the current transaction .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
            BEFORE COMMIT OR ROLLBACK<\/span> <\/span><\/span><\/p>\n
              \n
            • \n
              The transaction user will see the result of the change he made after SELECT and DML operations .<\/span> <\/span><\/span><\/p>\n<\/li>\n
            • \n
              Other users cannot see the result of the DML operations performed by the user doing the operation .<\/span> <\/span><\/span><\/p>\n<\/li>\n
            • \n
              The affected rows are locked , and other users cannot change the data on unaffected rows .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
              AFTER COMMIT<\/span> <\/span><\/span><\/p>\n
                \n
              • \n
                Data changes made in the database are applied permanently<\/span> .<\/span><\/span><\/p>\n<\/li>\n
              • \n
                All users can see the current result .<\/span> <\/span><\/span><\/p>\n<\/li>\n
              • \n
                The affected rows are unlocked .<\/span> <\/span><\/span><\/p>\n<\/li>\n
              • \n
                All SAVEPOINT points are deleted .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
                  \n
                • \n
                  **<\/span><\/span>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 .<\/span><\/p>\n<\/li>\n<\/ul>\n
                  \"\"<\/p>\n
                  DELETE FROM ADMIN.ISCI WHERE EMPLOYEE_ID=4;<\/em><\/pre>\n
                  \"\"<\/p>\n
                  SELECT * FROM ADMIN.ISCI;<\/em><\/pre>\n
                  We deleted the person with id above 4 , but this process did not happen permanently and now we commit it to make it permanent :<\/span><\/span><\/span><\/p>\n
                  COMMIT;<\/em><\/pre>\n
                  SELECT * FROM ADMIN.ISCI;<\/em><\/pre>\n
                  AFTER ROLLBACK<\/span> <\/span><\/span><\/p>\n
                    \n
                  • \n
                    Changes made to the data are undone .<\/span> <\/span><\/span><\/p>\n<\/li>\n
                  • \n
                    The records that are modified are released , the locks are removed .<\/span> <\/span><\/span><\/p>\n<\/li>\n
                  • \n
                    When uncommitted transaction Rollback is performed , unmodified data is retrieved from the Undo tablespace and the data becomes consistent .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
                    \"\"<\/p>\n
                    DELETE FROM ADMIN.ISCI;<\/em><\/pre>\n
                    ROLLBACK<\/em>;<\/pre>\n
                    Now we’re checking to see if it’s taken back :<\/span> <\/span><\/span><\/p>\n
                    SELECT * FROM ADMIN.ISCI;<\/em><\/pre>\n
                    FOR UPDATE<\/span><\/span><\/p>\n
                      \n
                    • \n
                      Locks selected records to prevent other users from changing them .<\/span> <\/span><\/span><\/p>\n<\/li>\n
                    • \n
                      Records are released when Rollback or Commit is performed .<\/span> <\/span><\/span><\/p>\n<\/li>\n
                    • \n
                      If more than one table is included in the join , all the selected records are locked in both tables .<\/span> <\/span><\/span><\/p>\n<\/li>\n
                    • \n
                      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 .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
                      See you in my next post.<\/b><\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
                      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 …<\/p>\n","protected":false},"author":10447,"featured_media":12287,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3019],"tags":[3011,1340,6841,1572,6811,6836,3000,3002],"class_list":["post-11873","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle-sql","tag-learn-oracle-sql","tag-oracle-sql","tag-oracle-sql-information","tag-sql","tag-sql-information","tag-sql-queries","tag-sql-tutorial","tag-what-is-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/dbms-sql-command.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11873","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/10447"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=11873"}],"version-history":[{"count":6,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11873\/revisions"}],"predecessor-version":[{"id":12271,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11873\/revisions\/12271"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/12287"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=11873"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=11873"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=11873"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}