Sybase 12.4.2 manual Deleting data, 225

CHAPTER 5 Moving Data In and Out of Databases

The SET clause

The WHERE clause

The FROM clause

The search condition need not refer to the column being updated. The company ID for Newton Entertainments is 109. As the ID value is the primary key for the table, you could be sure of updating the correct row using the following statement:

UPDATE customer

SET company_name = ’Einstein, Inc.’

WHERE id = 109

The SET clause specifies the columns to be updated, and their new values. The WHERE clause determines the row or rows to be updated. If you do not have a WHERE clause, the specified columns of all rows are updated with the values given in the SET clause.

You can provide any expression of the correct data type in the SET clause.

The WHERE clause specifies the rows to be updated. For example, the following statement replaces the One Size Fits All Tee Shirt with an Extra Large Tee Shirt

UPDATE product

SET size = ’Extra Large’

WHERE name = ’Tee Shirt’

AND size = ’One Size Fits All’

You can use a FROM clause to pull data from one or more tables into the table you are updating.

Deleting data

To remove data from a database, you can do any of the following:

•Use the DELETE statement to remove from a table all rows that meet the criteria you specify.

•Use the DROP TABLE statement to remove an entire table, including all data rows.

•Use the TRUNCATE TABLE statement to delete all rows from a table, without deleting the table definition.

For syntax of these statements, see the Adaptive Server IQ Reference Manual.

TRUNCATE TABLE is faster than a DELETE statement with no conditions.

225