Designing INSERT, UPDATE and DELETE queries
Later, in the Implementation Guide you can read about use of INSERT, UPDATE and DELETE queries. Sometimes it is necessary to design these queries before use. The following brief examples all use the sports centre scenario.
When using these design templates, it is not always necessary to include data in the new/updated values or criteria rows. It depends upon the problem that the query is being designed to resolve.
Designing an INSERT query
| Type of query | INSERT |
| Table | Sports area |
| New/updated value(s) | AreaID = 17, AreaName = Tennis Court 7, SurfaceID = 2, HireCost = 15, Available = True, Manager = Frankie Kane |
| Criteria |
| Type of query | Table |
|---|---|
| INSERT | Sports area |
| Type of query | New/updated value(s) |
|---|---|
| INSERT | AreaID = 17, AreaName = Tennis Court 7, SurfaceID = 2, HireCost = 15, Available = True, Manager = Frankie Kane |
| Type of query | Criteria |
|---|---|
| INSERT |
If implemented, this query would insert a new record into the Sports Area table, with the values for each attribute added to reflect those shown above.
Designing an UPDATE query
| Type of query | UPDATE |
| Table | Sports area |
| New/updated value(s) | Manager = Hannah Brooks |
| Criteria | AreaID = 17 |
| Type of query | Table |
|---|---|
| UPDATE | Sports area |
| Type of query | New/updated value(s) |
|---|---|
| UPDATE | Manager = Hannah Brooks |
| Type of query | Criteria |
|---|---|
| UPDATE | AreaID = 17 |
If implemented, this query would update the value of the Manager attribute (field) to become Hannah Brooks if the AreaID attribute (field) holds the value 17.
Designing a DELETE query
| Type of query | DELETE |
| Table | Sports area |
| New/updated value(s) | |
| Criteria | Manager = Lily McLuskey |
| Type of query | Table |
|---|---|
| DELETE | Sports area |
| Type of query | New/updated value(s) |
|---|---|
| DELETE |
| Type of query | Criteria |
|---|---|
| DELETE | Manager = Lily McLuskey |
If implemented, this query would delete the name Lily McLuskey from every record containing this name in the Manager field of the Sports Area table.
