Transcript CS 430 Database Theory

CS 430
Database Theory
Winter 2005
Lecture 13: SQL DML - Modifying Data
1
SELECT

Three statements for modifying data



INSERT - Add rows to a table
UPDATE - Update rows in a table
DELETE - Delete rows in a table
2
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
3
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
4
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
5
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)
6
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
7
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
8
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
9
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
10
Examples
11
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
12
More on Making Big Changes

And as always:



And when you’re going to do it …






Test, test, test
Try to do it in small steps
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
13