Lesson 15: Deleting Rows in SQL

You can use Scalable SQL to delete rows of data.


Syntax:

Delete from tablename
where fieldname =/<>/ ... value
and/or ... 
fieldname =/<>/ ... value
and/or
fieldname =/<>/ ... value

Explanation:

• If you do not include a Where clause, all of the rows in the table will be deleted.
• Every table has a primary key -- a field or combination of fields that uniquely identify each row in the table. VendId is the primary key for the vendor table. Each vendor is uniquely identified by the vendor Id. RefNbr is the primary key for APDoc.
• If you want to delete a single row of data, you can refer to the row in the Where clause by using the primary key.
• When deleting data, use the Start Transaction command so that any errors can be rolled back.
• Use the Rollback Work command to undo changes.
• Use the Commit Work command to finalize changes.

Examples:Delete vendor TV011 by executing the following commands.

1. To view the record, type:

Select *
from TrnVendor
where VendId = 'TV011';

2. To start the transaction, type:

Start transaction;

3. To delete the record, type:

Delete from TrnVendor
where VendId = 'TV011 ';

4. To check to make sure the records have been deleted, type:

Select *
from TrnVendor
where VendId = 'TV011';

5. To roll back the deletion, type:

Rollback work;

Delete all vendors with a current balance over 2000 dollars.

1. To view the records, type:

Select Name, CurrBal [-ZZZ,ZZZ.99]
from TrnVendor
where CurrBal > 2000;

2. To start the transaction, type:

Start transaction;

3. To delete the records, type:

Delete from TrnVendor
where CurrBal > 2000;

4. To check to make sure the records have been deleted, type:

Select Name, CurrBal [-ZZZ,ZZZ.99]
from TrnVendor
where CurrBal > 2000;

5. To roll back the deletion, type:

Rollback work;