CS 430 Database Theory

1. CS 430Database Theory Winter 2005 Lecture 13: SQL DML - Modifying Data

2. SELECT • Three statements for modifying data • INSERT - Add rows to a table • UPDATE - Update rows in a table • DELETE - Delete rows in a table

3. INSERT • First form: • INSERT INTO <table>[(<col1>, … <coln>)] VALUES (<list of values>); • Creates a new row in <table> with the specified values for the specified columns • Column list is optional, if omitted, the column order from CREATE TABLE is used (same as SELECT <table>.*) Not recommended. • Values can be an explicit value or the key word DEFAULT

4. INSERT, Form Two • Second form: • INSERT INTO <table> SET <col1>=<expr1>, <col2>=<expr2>, …; • Sets the specified columns to the results of the specified expressions • Expressions can refer to the values set in previous expressions • Expressions can be the keyword DEFAULT

5. INSERT, Form Three • Third form: • INSERT INTO <table>[(<col1>, …, <coln>)} SELECT …; • Inserts multiple rows into a table • As many as returned from SELECT • Matches the columns in <table> in order with the columns in the SELECT statement • If no columns are given for <table> uses the column order from the CREATE TABLE

6. Creating a [Temporary] Table • Approach 1: • CREATE TEMPORARY TABLE <t> (<defns>); • INSERT INTO <t> SELECT … ; • Approach 2: • CREATE TEMPORARY TABLE <t> SELECT … ; • Creates a temporary populated as per the SELECT statements • Definitions as part of the table creation are optional • Table column names are taken from SELECT (may need to create synonyms for column names)

7. UPDATE • UPDATE <table> SET <col1> = <expr1>, … WHERE <where condition>; • Modify the rows specified in the where condition • Modify the columns as indicated • Column names appearing in expressions are modified to their most recent values • Could be either the current value in the table or a value in an expression

8. Notes on Update • The following is legal: • UPDATE <table> SET age = 2 * age, age = age + 1; • Same as age = 2 * age + 1 • Complicated WHERE clauses usually require subqueries

9. More notes on update: • MySQL specific syntax: • UPDATE <table1>, <tablen> SET <col1> = … WHERE <where_condition>; • Can modify multiple tables simultaneously • Use <table_name>.<col_name> where needed • Can be used to substitute for a subquery

10. DELETE • DELETE FROM <table> WHERE <where_condition>; • Delete the matching rows from <table> • “DELETE FROM <table>;” Will empty table • MySQL: • DELETE FROM <t1>, …, <tn> USING <t1>, … , <tn>, <tn+1>, …, <ts> WHERE <where_condition>; • Delete from multiple rows simultaneously

11. Examples

12. Making Big Changes to the Database • Typical Scenario: • Applications are changing • As a result required structure of database needs to change • Need to reorganize the database • Approach: • Create new tables with new structures • Create new content in new tables • Select from old table, or • Select to flat file, modify flat file, Load data back to file • Drop old tables and rename new tables

13. More on Making Big Changes • And as always: • Test, test, test • Try to do it in small steps • And when you’re going to do it … • Take a weekend • Make a backup • Make the changes • Test the changes • Either restore the backup or go live • And if you have to do it live • Plan it in incremental steps changing applications and data together • Design applications to use both old and new data