List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 24 1999 7:55am
Subject:Re: Moving a column
View as plain text  
On Sat, 1999-09-25 00:24:22 +0100, Bobby DeRosa wrote:
(hmm, your system clock is wrong ...)

> I have a table called products. Product has 3 columns (serial, name, price)
> 
> I need to rearrange the columns so they look like this (name, serial, price)
> 
> How do I do this? Thanks

You always can change the order of columns with an appropriate SELECT
part:
  SELECT name, serial, price FROM yourtable;

But if you really have to change the table schema,
see chapter "7.7 ALTER TABLE syntax" of the MySQL Reference Manual.

I guess, you'd have to proceed like this:
 1. ALTER TABLE yourtable ADD COLUMN newname ...definition_of_name... FIRST;
 2. UPDATE yourtable SET newname=name;
 3. ALTER TABLE yourtable DROP COLUMN name;
 4. ALTER TABLE yourtable CHANGE COLUMN newname name ...definition_of_name...

But it's maybe better to create a complete new table:
 1. CREATE TABLE newtable ( name ..., serial ..., price ... );
 2. INSERT INTO newtable SELECT name, serial, price FROM yourtable;
 3. DROP TABLE yourtable;
 4. ALTER TABLE newtable RENAME AS yourtable;
With MySQL V3.23.x steps 1 and 2 become easier:
 1/2. CREATE TABLE newtable
        INSERT INTO newtable SELECT name, serial, price FROM yourtable;

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Moving a columnBobby DeRosa24 Sep
  • Re: Moving a columnMartin Ramsch24 Sep
  • Re: Moving a columnBob Kline24 Sep