UPDATING TABLES

Adding Rows to a Table

The INSERT command allows a user to add data to an existing table. Format 1 adds a single row to the table from the data listed in the INSERT command. Format 2 updates the table using data from another table.

Format 1:

    INSERT INTO {table-name | view-name} [(column-names)]
        VALUES (data-items)

Format 2:

    INSERT INTO {table-name | view-name} [(column-names)]
        select-statement.

table-name | view-name: the name of the table or view which is to be updated.

column-names:  one or more column-names, separated by commas, into which the data is to be inserted. If omitted, all columns of the table are assumed.

data-items:  list of values, separated by commas, for the columns indicated. The column-names are matched to the data-items on a one-to-one basis by position.

select-statement:  SELECT command used to select the data to be inserted from another table.

Examples:

To add a new manager to the MANAGER table:

insert into manager (managerno, managername, salary)
        values ('M4','Spencer',39500.50);

Assume a table called PORTM3 has been created with the same columns as the PORTFOLIO table. The following command will add all the rows from the current PORTFOLIO table with manager number M3:

insert into portm3 (portfoliono,managerno,cash)
        select portfoliono,managerno,cash
        from portfolio
        where managerno = 'M3'

Insert Statement with Check Option

With check option prohibits you from entering rows that are not in the subquery. If you try to insert a value of M6, with the following insert statement, it will not work.

insert into
        (select portfoliono,managerno,cash
        from portfolio
        where managerno = 'M3' with check option)
        values ('P015','M3', default);

Default uses a value previously specified as a default for the column when the table was created.

Updating Rows in a Table

The UPDATE command updates one or more columns in a table. All rows which satisfy the SELECT clause are updated.

Format:

UPDATE tablename
    SET column-name1 = {expression | NULL|subquery}
    [,column-name2 = {expression | NULL|subquery}...]
    [WHERE search-condition]

table-name: name of the table which is to be updated.

column-names: the names of the columns to be updated.

expression | NULL: the new values for the corresponding columns; use the keyword NULL to set the value to null.
search-condition: specifies which rows are to be updated.

Examples:

To change the price of stock symbol NTL to 52.50:

update stock
        set currentprice = 52.50
        where stocksymbol = 'NTL'

To change the name of manager M3 to James with a salary of $44,900:

update manager
        set managername = 'James', salary = 44900
        where managerno = 'M3';

To increase all salaries in the manager table by $1000:

update manager
        set salary = salary +1000;

Deleting Rows from a Table

The DELETE command deletes one or more rows from a given table. All rows that satisfy the search condition are deleted from the table. If the WHERE clause is omitted, all rows are deleted.

Format:

DELETE FROM table-name
[WHERE search-condition]

table-name: name of the table from which the rows are to be deleted.

search-condition: specifies which rows are to be deleted.

Example:

To delete all the holdings in the holding file containing stock symbol JDS:

delete from holding
        where stocksymbol = 'JDS'

Merge

Merge provides the ability to conditionally update or insert data.

Example:

To insert or update, depending on the existence of a row in the manager archive table:

    Merge into archive_manager am
       using manager m
       on (am.managerno = m.managerno)
    when matched then
        update set
am.managername=m.managername,
am.salary = m.salary
     when not matched then
        insert values
        (m.managerno, m.managername, m.salary);