Entering content frame

This graphic is explained in the accompanying text Inserting, Changing, and Deleting Rows Locate the document in its SAP Library structure

SQL statements for data manipulation (inserting, changing, and deleting rows) are described in the following section.

Inserting Rows

To insert rows, use the INSERT statement.

 

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

or

INSERT INTO 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.

...

    1.      If necessary, drop the person table.
DROP TABLE person

    2.      Create the person table:
CREATE TABLE person
(pno FIXED(6,0) PRIMARY KEY,
 name CHAR(20),
 city CHAR(20))

       3.      Fill the table with values from the city table.
INSERT INTO person
SELECT zip, state, name FROM 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 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:

INSERT Statement (insert_statement)

 

Changing Rows

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

 

UPDATE 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 person

Result

PNO

NAME

CITY

10019

CITY

New York

10580

NY

New York

11788

NY

Long Island

12203

NY

Albany

 

See also:

UPDATE Statement (update_statement)

 

Deleting Rows

To delete rows, use the DELETE statement.

 

DELETE FROM 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 person

Result

PNO

NAME

CITY

10019

CITY

New York

 

See also:

DELETE Statement (delete_statement)

 

Leaving content frame