1 / 15

Chapter 14. Changing Data: Insert, Update, Delete

Chapter 14. Changing Data: Insert, Update, Delete. Presented by Victor M. Matos. Changing Data. In this Chapter you will learn how to insert new rows into a table update the values of columns in a row, and delete rows from a table. Sample Data. City SampleDate Noon MidNight

steven-goff
Download Presentation

Chapter 14. Changing Data: Insert, Update, Delete

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 14.Changing Data:Insert, Update, Delete Presented by Victor M. Matos Oracle8 - The Complete Reference. Koch & Loney

  2. Changing Data • In this Chapter you will learn how to • insert new rows into a table • update the values of columns in a row, and • delete rows from a table. Oracle8 - The Complete Reference. Koch & Loney

  3. Sample Data City SampleDate Noon MidNight Precipitation • COMFORT Table SQL> select * from COMFORT; CITY SAMPLEDAT NOON MIDNIGHT PRECIPITATION ------------- --------- --------- --------- ------------- SAN FRANCISCO 21-MAR-93 62.5 42.3 .5 SAN FRANCISCO 22-JUN-93 51.1 71.9 .1 SAN FRANCISCO 23-SEP-93 61.5 .1 SAN FRANCISCO 22-DEC-93 52.6 39.8 2.3 KEENE 21-MAR-93 39.9 -1.2 4.4 KEENE 22-JUN-93 85.1 66.7 1.3 KEENE 23-SEP-93 99.8 82.6 KEENE 22-DEC-93 -7.2 -1.2 3.9 Oracle8 - The Complete Reference. Koch & Loney

  4. Insert • Adding a new row to the COMFORT table insert into COMFORT values ( 'CLEVELAND', TO_DATE('30-JAN-1999', 'DD-MON-YYYY'), 56.7, 43.8, 0); 1 row created CLEVELAND 30-JAN-99 56.7 43.8 0 Oracle default date-format Oracle8 - The Complete Reference. Koch & Loney

  5. Insert • Adding another row to the COMFORT table insert into COMFORT values ( 'CLEVELAND', TO_DATE(’01/31/1999', ’MM/DD/YYYY'), 56.7, 43.8, 0); 1 row created CLEVELAND 31-JAN-99 56.7 43.8 0 Change non-Oracle date values using TO_DATE(…). Indicate current date structure. Oracle8 - The Complete Reference. Koch & Loney

  6. Insert • Adding a time insert into COMFORT values ( 'CLEVELAND', TO_DATE('01/29/1999 1:35', 'MM/DD/YYYY HH24:MI'), 56.7, 43.8, 0); 1 row created CLEVELAND 29-JAN-99 1:10 56.7 43.8 0 Change non-Oracle date values using TO_DATE(…). Indicate current date structure. Oracle8 - The Complete Reference. Koch & Loney

  7. Insert • Giving an explicit list of columns. insert into COMFORT (SampleDate, Precipitation, City, Noon, Midnight) values ( TO_DATE('01/29/1999 1:35', 'MM/DD/YYYY HH24:MI'), NULL, 'CLEVELAND', 56.7, 43.8,); A different sequence of fields NULL means ‘unknown-value’ Oracle8 - The Complete Reference. Koch & Loney

  8. Insert • Using a SELECT command to insert rows. insert into COMFORT (SampleDate, Precipitation, City, Noon, Midnight) select TO_DATE('31-JAN-1999', 'DD-MON-YYYY'), Precipitation, 'CLEVELAND', Noon, Midnight from COMFORT where City = 'KEENE' and SampleDate='22-DEC-93' / KEENE 22-DEC-1993 12:00 -7.2 -1.2 3.9 CLEVELAND 31-JAN-1999 12:00 -7.2 -1.2 3.9 Oracle8 - The Complete Reference. Koch & Loney

  9. DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO WORKS_ON ESSN PNO HOURS Insert 1/2 • Use a SELECT command to add all the employees from the ‘RESEARCH’ dept. to the new project ‘X17’. Assign them to 1.5 hours/week. Oracle8 - The Complete Reference. Koch & Loney

  10. 333445555 7777 1.5 123456789 7777 1.5 666884444 7777 1.5 453453453 7777 1.5 New records Insert 2/2 • SQL solution insert into WORKS_ON(Essn, Pno, Hours) select Ssn, 'X17', 1.5 from EMPLOYEE where Dno IN ( select Dnumber from DEPARTMENT where Dname LIKE 'RESEARCH%' ) Oracle8 - The Complete Reference. Koch & Loney

  11. Update update COMFORT set Precipitation = .5, MidNight = 73.1 where City = 'KEENE' AND SampleDate = '22-DEC-1993’ KEENE 22-DEC-93 -7.2 73.1 .5 Oracle8 - The Complete Reference. Koch & Loney

  12. Update • Add/Subtract to a field. update COMFORT set Noon = Noon + 10, MidNight = MidNight - 20 where City = 'KEENE' AND SampleDate = '22-DEC-1993’ KEENE 22-DEC-93 -7.2 73.1 .5 KEENE 22-DEC-93 2.8 53.1 .5 Oracle8 - The Complete Reference. Koch & Loney

  13. Update • Use SELECT comand Two attributes at once! update COMFORT set (Noon, MidNight) = (select Humidity, Temperature from WEATHER where City = 'MANCHESTER') where City = 'KEENE' Oracle8 - The Complete Reference. Koch & Loney

  14. Delete • Remove the city of ‘San Francisco’. delete from COMFORT where City = 'SAN FRANCISCO' • Recall the deleted records. RollBack; Oracle8 - The Complete Reference. Koch & Loney

  15. Delete • Remove ALL cities. delete from COMFORT; Table definition is still in the dictionary • Recall the deleted records. RollBack; Oracle8 - The Complete Reference. Koch & Loney

More Related