UPDATE statement changes existing data in a table.
Syntax

UPDATE <table name>
   SET {column = {expression
    | NULL
    | DEFAULT},...}
    [ {WHERE <predicate>}];

With a single UPDATE statement, arbitrary number of columns can change their values. However a column cannot be changed in the same UPDATE statement more than once. All rows of a table will be affected if a WHERE clause is eliminated from an UPDATE statement.

If the column is defined to allow NULL values, NULL can be specified explicitly. Moreover, existing value can be changed by default value (DEFAULT) for a given column.

An expression may refer to current values in a table to be changed. For example, we may decrease prices by 10 percent on all the laptops with the following statement:

UPDATE Laptop SET price=price*0.9;

The values of one column are allowed to assign to another column. For example, it is required to change HD less than 10 Gb in laptops. New HD capacity must be one-half of available RAM. The solution may be as follows:

UPDATE Laptop SET hd=ram/2 WHERE hd<10;

Clearly, data types of the hd and ram columns need to be compatible. The CAST expression may be used for conversion.

If the data needs to be changed depending on contents of some column, we can use the CASE expression. Say, if it is needed to setup HD with capacity of 20 Gb in laptops with RAM less than 128 Mb and HD with capacity of 40 Gb in remaining laptops, we can write the query:

UPDATE Laptop    SET hd = CASE WHEN ram<128 THEN 20 ELSE 40 END

Use of subquery is also allowed to define the column value. For example, we need to complete all laptops with most fast available processor. Then we write:

UPDATE Laptop    SET speed = (SELECT MAX(speed) FROM Laptop)

Some words about autoincrementable columns ought to be said. If the code column in the Laptop table is defined as IDENTITY(1,1), then the following statement

UPDATE Laptop SET code=5 WHERE code=4

will not be executed, since autoincrementable column does not allow modification, but execution error will be returned. To solve above task, we can proceed as follows. At first, let us insert the needed row using SET IDENTITY_INSERT, then delete the old row:

SET IDENTITY_INSERT Laptop ON INSERT INTO Laptop_ID(code, model, speed, ram, hd, price, screen)    SELECT 5, model, speed, ram, hd, price, screen    FROM Laptop_ID WHERE code=4 DELETE FROM Laptop_ID WHERE code=4

Clearly, another row with the value code=5 must not be in the table.

Transact-SQL UPDATE statement extends the Standard at the cost of using the optional FROM clause. This clause specifies a table that is used to provide the criteria for the update operation. This extension gives additional flexibility specifying a join that can be used instead of a subquery in the WHERE clause to identify rows to be updated.

Example. Let us write "No PC" in the type column for those PC models in the Product table that have not corresponding rows in the PC table. The solution through table join may be written as:

UPDATE Product    SET type='No PC'    FROM Product pr LEFT JOIN PC ON pr.model=pc.model    WHERE type='pc' AND pc.model IS NULL

Here, we use outer join that results in the pc.model column contains NULL values for those PC models that are absent from the PC table, which is used for providing the criteria for the update operation. Clearly, this task has also a "standard" solution:

UPDATE Product    SET type='No PC'    WHERE type='pc' AND model NOT IN (SELECT model FROM PC)

DELETE statement

DELETE statement delete rows from temporary or basic tables, views and cursors, with the statement action in the two last cases is propagated on those basic tables, from which were extracted data into these views and cursors.

Syntax:

DELETE FROM <table name> [WHERE <predicate>];

If the WHERE clause is absent, all rows from the table or view will be deleted (the view must be updatable). Deleting the all rows from a table in Transact-SQL can be also done (faster than a DELETE statement) by using the TRUNCATE TABLE statement.

Syntax

TRUNCATE TABLE <table name>

Some distinctions are in the realization of the TRUNCATE TABLE statement, which one should keep in mind:
1. TRUNCATE TABLE statement does not record an entry in the transaction log for each deleted row, only the page deallocations are recorded in the transaction log.
2. Triggers do not fire.
3. This statement cannot be used on a table referenced by a FOREIGN KEY constraint.
4. The counter used by an IDENTITY for new rows is reset to the seed for the column.

Example. Needed to delete from the Laptop table all the laptops with the screen size less than 12 in.

DELETE FROM Laptop    WHERE screen < 12;

All laptops we can delete with the query

DELETE FROM Laptop

or

TRUNCATE TABLE Laptop

Transact-SQL statement extends the DELETE statement over Standard introducing additional FROM clause.

FROM <table source>

This extension allows us to specify data to be deleted from the table in the first FROM clause.

This extension gives additional flexibility specifying a join that can be used instead of a subquery in the WHERE clause to identify rows to be deleted.

Example. Let us need to delete the PC models from the Product table that have not corresponding rows in the PC table.

Using standard syntax, the task may be solved with the query:

DELETE FROM Product    WHERE type='pc' AND model NOT IN (SELECT model FROM PC)

Note that the predicate type='pc' is necessary here; otherwise printer and laptop models will be deleted.

This task can be solved through the additional FROM clause as follows:

DELETE FROM Product    FROM Product pr LEFT JOIN PC ON pr.model=pc.model    WHERE type='pc' AND pc.model IS NULL

Here, we use outer join that results in the pc.model column contains NULL values for those PC models that are absent from the PC table, which is used for providing the criteria for the delete operation.