Updating joined tables average length of a dating relationship
But you can also update whole sets of records at once, and in very powerful ways. For example, you can delete rows in one table depending on whether or not they exist in another table.
SQL even has a way, a relatively new addition to the standard, by which you can insert, update, and delete all at once. For example, you want to insert a new record into the DEPT table.
Typically, when you do not wish to specify a value for a column, you leave that column out of your column and values lists: Here, no value for ID is specified.
Many would expect the column to taken on the null value, but, alas, a default value was specified at table creation time, so the result of the preceding INSERT is that ID takes on the value 0 (the default).
By specifying NULL as the value for a column, you can set the column to NULL despite any default value.
You want to copy rows from one table to another by using a query.
The query may be complex or simple, but ultimately you want the result to be inserted into another table.
For example, you want to copy rows from the DEPT table to the DEPT_EAST table.
Simply follow the INSERT statement with a query that returns the desired rows.
Many times, however, it is more efficient to use a set-based approach to create new rows.
To that end, you’ll also find techniques for inserting many rows at a time.
In the solution provided, the expression “1 = 0” in the WHERE clause of the query causes no rows to be returned.
Thus the result of the CTAS statement is an empty table based on the columns in the SELECT clause of the query.