Home arrow Articles arrow Interactive Paradox arrow Local SQL Part 9: Adding, Updating, and Deleting Data
26 November 2014
 
 
Local SQL Part 9: Adding, Updating, and Deleting Data Print E-mail
Written by Larry DiGiovanni   
20 June 2002
Add, modify and delete data with these simple SQL techniques!Local SQL Part 9
Adding, Updating, and Deleting Data
2002 Lawrence G. DiGiovanni
Digico, Inc.
www.digicoinc.com


Preface

Local SQL is a very powerful yet commonly underutilized tool that is provided with Paradox (as part of the Borland Database Engine). It exposes the power and flexibility of the SQL sublanguage to both Paradox developers as well as interactive end-users.

This is the ninth in a planned series of articles on using Local SQL with Paradox/BDE applications. This article discusses how to use SQL statements to add, update, and delete table data.


Introduction

Like QBE, Local SQL provides the ability to modify the contents of a table, either by adding rows, updating the column values of existing rows, or deleting rows. These modifications can be based on data in other tables.

As with the last article, our examples will use the DBDEMOS database, which contains the Biolife example database.


INSERT

The INSERT statement is used to add rows to a table. There are two forms to this statement:
1. INSERT INTO <table> {(<column-list>)} VALUES (<value-list>)
And
2. INSERT INTO <table> {(<column-list>)} <select-statement>
The first form is used to insert rows containing literal values, one at a time, into a table. The second form is used to insert one or more rows derived from another query (subquery). In both cases, primary keys and unique indexes are enforced, and the INSERT will fail if it attempts to violate one of these constraints. The same is true if data type restrictions are not observed. As with QBE queries, not all validity checks are enforced with SQL queries. For instance, picture formats are not enforced.

In both forms, if values are specified for all columns of the table in the order they appear in the field roster, the <column-list> may be omitted, and the values specified are applied in field roster order.

So, let's say we'd like to add a new employee to the employee table. We could use the following SQL INSERT statement:
INSERT INTO employee (EmpNo, LastName, FirstName,
                      PhoneExt, HireDate, Salary)
            values (200, 'DiGiovanni','Larry','000',
                    '12:00:00 AM, 1/21/1998',1000)
And a new row is added with the values shown above. An inserted table is also created with the same data. Note that if we attempt to execute the same query again (which would create a primary key violation on EmpNo), the query fails, and the record is added to the errorins table.

We can also perform an INSERT based on a SELECT statement. The SELECT statement can be any valid select statement that returns the proper data types in the proper order as specified in the <column-list> of the INSERT statement. That means we can include columns from multiple joined tables as well as literal values.

For this example, make a copy of the employee table called empins.
INSERT INTO employee (EmpNo, LastName, FirstName,
                      PhoneExt, HireDate, Salary)
            SELECT EmpNo + 200, LastName, FirstName,'000',
                   HireDate, Salary + 1000
              FROM empins
Note that we've used straight columns, columns in calculations, and literals in this INSERT. We've created new rows in the employee table based on the current data (in the copy we created) with new EmpNos, explicit phone extensions (000), and higher salaries (Salary + 1000). Also note that if we execute this query again, the INSERT fails due to key violations.


DELETE

The DELETE statement is used to remove rows from a table. The form of this statement is:
DELETE FROM <table> {WHERE <condition-list>}
Note that there is no <column-list>. This makes sense because the DELETE statement operates on the whole row. The WHERE clause allows us to limit the DELETE to only those rows that meet a specific condition. Omitting the WHERE clause makes the DELETE statement delete all rows of the table.

So let's delete those extra rows we added in the above example.
DELETE FROM employee WHERE EmpNo > 200
Executing the above DELETE statement will delete the rows we added in the previous example, placing them in a deleted table just as if we'd done this through a QBE delete.

Local SQL does not support a joined DELETE, either to delete rows from two tables at a time, or to delete rows from one table which satisfy some join condition to another table. We can reference other tables in the WHERE clause using a subquery, though, to achieve the same effect.
DELETE FROM employee WHERE EmpNo NOT IN (SELECT empno FROM empins)
The above query deletes all rows from employee that do not have a corresponding row in empins (the temporary table we created earlier).


UPDATE

The UPDATE statement is used to modify values in existing rows of a table. There are two forms to this statement:
1. UPDATE <table> set <column-name> = <expression> {, <column-name> = <expression>}
And
2. UPDATE <table> set <column-name> = (SELECT <expression> FROM <table>
                                       {WHERE <condition-list>}}
As was the case with the INSERT statement, update expressions must match the data type and restrictions of their target columns.

Let's use the temporary empins table we created earlier, and change all salaries and phone extensions using Form 1 above:
UPDATE empins SET Salary = Salary - 1000,
                  PhoneExt = '000'
 WHERE empno > 100
Now all salaries for employees with empno > 100 have been reduced by $1,000, and all phone extensions are '000'.

Local SQL does not support a joined UPDATE, either to update columns from two tables at a time, or to update columns from one table which satisfy some join condition to another table. We can reference other tables in the WHERE clause using a subquery, though, to achieve the same effect.

Now let's set the salaries and extensions we changed above back to their original values, using Form 2 above:
UPDATE empins e SET e.Salary = (SELECT Salary
                                  FROM employee e1
                                 WHERE e1.EmpNo = e.EmpNo),
                    e.PhoneExt = (SELECT PhoneExt
                                  FROM employee e2
                                 WHERE e2.EmpNo = e.EmpNo)
 WHERE e.empno > 100
Note that we used table aliases to identify tables in the WHERE clauses in the subqueries.

Note also that we used two separate SET clauses, separated by a comma, with two independent subqueries. Local SQL does not allow UPDATE statements to update multiple columns in a single SET clause. Many other dialects do support this capability.


Summary

The SQL INSERT, DELETE, and UPDATE statements are used to manipulate the data in tables. Data may be inserted, updated and deleted based on conditions involving multiple tables through subqueries, but not joins. Data may be inserted and updated based on literal values, subqueried column values, or combinations of both.


Local SQL Part 10
< Prev   Next >
 
Top! Top!