Minder Chen, Ph.D.

1
SQL Language DML (Data Manipulation) Insert,
Update, Delete

• Minder Chen, Ph.D.

2
INSERT

• INSERT INTO table-name view-name
• column-name1, column-name2, )
• VALUES (value1, value2, )
• Adding one or more row(s) into an existing table.
  
• Table with Foreign Keys
• Target (referenced column) of foreign key must
  exist
• NULL value of the foreign key (referencing
  column) allowed
• CHECK Constraint
• INSERT INTO DIVISION
• VALUES('D010',52,'ADMINISTRATION')

3
INSERT Examples

• INSERT INTO DIVISION
• VALUES('D060',NULL,'MARKETING')
• INSERT INTO DIVISION (DIV_NAME, D_ID)
• VALUES('RD', 'D070')
• D_ID DIR DIV_NAME
• ---- --------- ----------------
• D010 52 ADMINISTRATION
• D020 1 COMPUTER SCIENCE
• D030 1 ACCOUNTING
• D040 1 STATISTICS
• D050 1 MATHEMATICS
• D060 MARKETING
• D070 RD

4
INSERT Examples

• INSERT INTO
• INSTRUCTOR (L_NAME, M, F_NAME, SALARY)
• VALUES ('Smith', 'F', 'Bob', 6000)
• ERROR at line 1
• ORA-01400 cannot insert NULL into
  ("PO8"."INSTRUCTOR"."I_NO")
• Correct statement
• INSERT INTO
• INSTRUCTOR (I_NO, L_NAME, M, F_NAME, SALARY)
• VALUES (60, 'Smith', 'F', 'Bob', 6000)

5
SEQUENCE in Oracle

• DROP SEQUENCE STUDENT_SEQ
• CREATE SEQUENCE STUDENT_SEQ
• START WITH 6000
• INCREMENT BY 10
• SELECT STUDENT_SEQ.currval
• FROM DUAL
• This example increments the student sequence and
  uses its value for a new student inserted into
  the STUDENT table
• INSERT INTO STUDENT
• VALUES (STUDENT_SEQ.nextval, 'Jack Smith', '1
  Main St.','Fairfax', 'VA', 22030, '7039931999')

6000
6
Server Generated Sequence Numbers

• In some database servers such as Sybase, the
  system maintains a sequence number for an
  identity column. It is special data type called
  AutoNumber is Access.
• Create table employee (
• id numeric(4, 0) identify,
• name char(20) not null,
• title char(50) not null)
• Insert into employee values ('Minder', 'Dean')
• Insert into employee values ('Justin',
  'Instructor')
• Result
• id name title
• 1 Minder Dean
• 2 Justin Instructor

7
INSERT Multiple Rows

• INSERT INTO table-name view-name
• column-name1, column-name2, )
• SELECT statement
• CREATE TABLE TEACHER1
• (I_NO NUMBER(4) NOT NULL,
• F_NAME VARCHAR(8) NOT NULL,
• L_NAME VARCHAR(5) NOT NULL,
• DIV CHAR(4))
• INSERT INTO TEACHER1 (I_NO, F_NAME, L_NAME, DIV)
• SELECT I_NO, F_NAME, L_NAME, DIV FROM INSTRUCTOR
  WHERE TITLE 'TEACHER'

8
UPDATE Modify One or More Columns

• UPDATE table-name view-name
• SET column-name1 expression1, column-name2
  expression2, ...
• WHERE clause
• - - Change the last name of the instructor (I_NO
  21) to 'WANG'
• UPDATE INSTRUCTOR
• SET L_NAME'WANG'
• WHERE I_NO 21
• - - Give all the instructors in D010 department a
  10 raise.
• UPDATE INSTRUCTOR
• SET SALARY SALARY 1.1
• WHERE DIV 'D010'
• I_NO F_NAME M L_NAM SALARY
• --------- ------------- --- -----------
  -------------
• 1 PATRICK D LEE 72000
• 19 ALBERT Y STONE 60000
• 21 BRUCE C WANG 48000

9
UPDATE

• You cannot update a view if the view's defining
  query contains one of the following constructs
• join
• set operator
• GROUP BY clause
• group function
• DISTINCT operator
• Non-columns are used in SELECT clause.

10
DELETE Deleting One or More Rows

• DELETE FROM table-name WHERE clause
• EXAMPLE
• - - Delete instructor whose I_NO is 52
• DELETE FROM INSTRUCTOR
• WHERE I_NO 52
• - - delete students with 301 area code
• DELETE FROM STUDENT
• WHERE TEL_NO LIKE '301'
• - - delete the whole table
• DELETE FROM DIVISION
• NOTES DELETE command is a very powerful command,
  please use it with care.