Inserting, Updating and Deleting Rows

SQL statements for data manipulation (insert, update, and delete) are described in the following section.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Inserting Rows

To insert rows, use the INSERT statement.

INSERT INTO hotel.city VALUES ('10018','New York','NY')

INSERT INTO hotel.city (zip,name,state) VALUES ('10017','New York','NY')

Rows are inserted in the hotel table (INSERT). The values that are to be inserted after the keyword VALUES are specified in parentheses. If you do not specify a column name, the order of the values must match the order of the definitions in the columns. Both orders must have the same length and data type. You can write undefined values as NULL.

You can also copy data from one table to another using a SELECT statement.

Delete the person table, if necessary.

DROP TABLE hotel.person
Create the person table:

CREATE TABLE hotel.person

(pno FIXED(6.0) PRIMARY KEY,

name CHAR(20),

city CHAR(20))
Fill the table with values from the city table:

INSERT INTO hotel.person

SELECT zip, state, name FROM hotel.city WHERE zip < '20000'

The person table is filled with information from the city table. You can check this using the following SELECT statement, for example:

SELECT pno, name state, city FROM hotel.person

Result

`PNO`	`STATE`	`CITY`
`10017`	`NY`	`New York`
`10018`	`NY`	`New York`
`10019`	`NY`	`New York`
`10580`	`NY`	`New York`
`11788`	`NY`	`Long Island`
`12203`	`NY`	`Albany`

See also: SQL Reference Manual, INSERT Statement (insert_statement)

Updating Rows

To update rows, use the UPDATE statement. Note that all the rows in the table are updated if the WHERE clause is missing from the UPDATE statement.

UPDATE hotel.person

SET name = 'CITY'

WHERE pno = '10019'

In the person table, the column value for name is changed (UPDATE...SET...); however, only for the row specified in the WHERE clause. You can check this using the following SELECT statement, for example:

SELECT pno, name, city

FROM hotel.person

Result

`PNO`	`NAME`	`CITY`
`10017`	`NY`	`New York`
`10018`	`NY`	`New York`
`10019`	`CITY`	`New York`
`10580`	`NY`	`New York`
`11788`	`NY`	`Long Island`
`12203`	`NY`	`Albany`

See also: SQL Reference Manual, UPDATE Statement (update_statement)

Deleting Rows

To delete rows, use the DELETE statement.

DELETE FROM hotel.person

WHERE name = 'NY'

In the person table, rows are deleted (DELETE FROM...), that is, all the rows with the entry NY as the name (WHERE...) are deleted. You can check this using the following SELECT statement, for example:

SELECT pno, name, city

FROM hotel.person

Result

`PNO`	`NAME`	`CITY`
`10019`	`CITY`	`New York`

See also: SQL Reference Manual, DELETE Statement (delete_statement)

More Information

Data Manipulation