SQL note (1) – INSERT

Today, I tried to update the structure of database and learnt something new about insert statement. Share it with abstract example.

Now we got a `interest_rate` table in database contains these columns: `currency`, `interest_rate`. It indicated the relation of interest rate and currency. One day, we find that the interest rate is only relate to currency but we want also `period` for the fixed deposit.

It is a database and contains so many data, so we can’t change it manually. We just want to write a SQL script and run it.

Surely, this process works.

  1. change the structure of database and insert data if necessary;
  2. do the code improvement;
  3. then the system should act exactly the same as before;
  4. use the background control panel to modify the data when necessary.

It’s nice because it just need one hour to upgrade and then the whole system works fine. It’s online without database export, changing, and import, which are very danger and result a quite long downtime.

Then let’s focus on how to upgrade database structure and insert records for this example. I’m using MySQL and assume that there’s a table contains all possible `period` values.

# create a temporary table
CREATE TABLE `interest_rates_tmp` (
  `currency` VARCHAR(3) NOT NULL,
  `rate` INT NOT NULL
);

# and copy data
INSERT INTO `interest_rates_tmp`
  SELECT * FROM `interest_rates`;

# truncate the old table
TRUNCATE TABLE `interest_rates`;

# add a new column to the table
ALTER TABLE `interest_rates`
  ADD `period` INT NOT NULL AFTER `currency`;
ALTER TABLE `interest_rates`
  DROP INDEX `currency`,
  ADD UNIQUE `currency_period` (`currency`, `period`);

# insert the cartesian product of the `interest_rates`
# table and the `periods` table
INSERT INTO `interest_rates` (`currency`, `period`, `rate`)
  SELECT `currency`, `periods`.`value` AS `period`, `rate`
  FROM `interest_rates_tmp`, `periods`;

# drop the temporary table
DROP TABLE `interest_rates_tmp`;

I got little knowledge about the INSERT statement with SELECT subquery before. Even I didn’t know it’s a valid SQL script or not. So save it here.

Post by : cooper

Post under : Coding, , ,

on September 15th, 2009 | No Comments »