Updating joined tables
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.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.
As a shortcut, you can omit the column list in an INSERT statement: of the columns in the table, and be mindful of the order of the values in the VALUES list; you must supply values in the same order in which the database displays columns in response to a SELECT * query.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.The following example illustrates the use of the DEFAULT keyword: The DEFAULT keyword in the values list will insert the value that was specified as the default for a particular column during table creation. My SQL, Postgre SQL, and SQL Server users have another option available if all columns in the table are defined with a default value (as table D is in this case).You may use an empty VALUES list (My SQL) or specify the DEFAULT VALUES clause (Postgre SQL and SQL Server) to create a new row with all default values; otherwise, you need to specify DEFAULT for each column in the table.