Record maintenance includes looking
for one or more records, modifying one or more records, or deleting one or
more records.
In SQL, to delete a record, use the DELETE FROM statement associated
with the WHERE
operator. The formula to follow is:
DELETE FROM TableName WHERE Condition(s)
The TableName factor is used to identify a
table whose record(s) would be removed.
The Condition(s) factor allows you to identify
a record or a group of records that carries a criterion. Make
sure you are precise in your criteria so you would not delete the wrong
record(s).
Here is an example used to remove a particular record from the
table:
Private Sub Exercise_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
Using connection As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='VideoCollection';" & _
"Integrated Security=yes;")
Dim command As SqlCommand = _
New SqlCommand("DELETE FROM Videos " & _
"WHERE [Video Title] = 'The Lady Killers';", _
connection)
connection.Open()
command.ExecuteNonQuery()
MsgBox("The video title ""The Lady Killers"" has been deleted")
End Using
End Sub
To delete a group or records, apply the
DELETE FROM table formula and use a WHERE
condition that can identify each one of the records.
To clear a table of all records, use the DELETE
operator with the following formula:
DELETE TableName;
When this statement is executed, all records from the TableName
factor would be removed from the table. Here is an example:
Private Sub btnClose_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles btnClose.Click
Dim strConnection As String = _
"Data Source=(local);" & _
"Database='CPAR1';" & _
"Integrated Security=yes;"
Dim strRepairOrder As String = "DELETE RepairOrders;"
Using connection As SqlConnection = _
New SqlConnection(strConnection)
Dim command As SqlCommand = New SqlCommand(strRepairOrder, connection)
connection.Open()
command.ExecuteNonQuery()
MsgBox("All repair orders have been deleted.")
End Using
Close()
End Sub
To support record maintenance operations, the SQL provides the UPDATE keyword
that is used to specify the table on which you want to maintain the
record(s). The basic formula to use is:
UPDATE TableName SET ColumnName = Expression
With this formula, you must specify the name of the
involved table as the TableName factor of our formula. The SET
statement allows you to specify a new value, Expression, for the
field under the ColumnName column.
The primary formula of the UPDATE statement as introduced
on our formula can be used to update all records. Here is an example:
Private Sub Exercise_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
Using connection As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='VideoCollection';" & _
"Integrated Security=yes;")
Dim command As SqlCommand = _
New SqlCommand("UPDATE Videos SET Rating = 'R';", _
connection)
connection.Open()
command.ExecuteNonQuery()
MsgBox("All video records have been rated R.")
End Using
End Sub
With this code, all records of the Videos table will have
their Rating fields set to a value of R.
You must provide a way for the interpreter
to locate the record. To do this, you would associate the WHERE operator
in an UPDATE statement using the following formula:
UPDATE TableName SET ColumnName = Expression WHERE Condition(s)
The WHERE operator allows you to specify how the
particular record involved would be identified. It is very important, in
most cases, that the criterion used be able to uniquely identify the record. In
the above table, imagine that you ask the interpreter to change the released
year to 1996 where the director of the video is Rob Reiner. The UPDATE statement
would be written as follows:
UPDATE Videos SET YearReleased = 1996 WHERE Director = 'Rob Reiner';
In the above table, there are at least two videos directed
by Rob Reiner. When this statement is executed, all video records whose director
is Rob Reiner would be changed, which would compromise existing records that did
not need this change. Therefore, make sure your WHERE statement would isolate
one particular record or only those that need to be updated. Here is an example
used to change the name of the director of a particular video:
Private Sub Exercise_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles Me.Load
Using connection As SqlConnection = _
New SqlConnection("Data Source=(local);" & _
"Database='VideoCollection';" & _
"Integrated Security=yes;")
Dim command As SqlCommand = _
New SqlCommand("UPDATE Videos " & _
"SET Director = 'Jonathan Lynn' " & _
"WHERE [Video Title] = 'The Distinguished Gentleman';", _
connection)
connection.Open()
command.ExecuteNonQuery()
MsgBox("The director of 'The Distinguished Gentleman' " & _
"video has been updated.")
End Using
End Sub
|
Introduction to the Records of a Database
Subscribe to:
Post Comments (Atom)
Related Scripting
No comments:
Post a Comment