Prev: Module positioning
Next: SELECT / ORDER BY
From: "Ron Piggott" on 7 Sep 2010 05:29
I am wondering if something like the following will work in mySQL:

ALTER TABLE `stats` ADD `visits` INT( 25 ) NOT NULL AUTO_INCREMENT PRIMARY
KEY FIRST ORDER BY `initial_access` ASC

This particular syntax won't work though.

initial_access is a column that contains a unix timestamp.

I am trying to get the auto_increment value to be added in order of
sequence of when the visits occurred.

Thank you.

Ron

From: "Adriano Rodrigo Guerreiro Laranjeira" on 7 Sep 2010 09:56
Hey friend!

I can't see another way to fix your table without a processing (like a
stored procedure, script, etcetera).

But I believe there is a workaround, which involves a creation of
another table. See this:
mysql> CREATE TABLE stats2 LIKE stats;
mysql> ALTER TABLE stats2 ADD COLUMN id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY(id);
mysql> INSERT INTO stats2 SELECT *, 0 FROM stats ORDER BY
initial_access;

Don't forget to see the correct type of INT which should be used.


Cheers,
Adriano!
> > > > > > > >
> On Tue, 7 Sep 2010 05:29:07 -0400
> "Ron Piggott" <ron.piggott(a)actsministries.org> wrote:
> I am wondering if something like the following
> will work in mySQL:
>
> ALTER TABLE `stats` ADD `visits` INT( 25 )
> NOT NULL AUTO_INCREMENT
> PRIMARY
> KEY FIRST ORDER BY `initial_access` ASC
>
> This particular syntax won't work though.
>
> initial_access is a column that contains a unix timestamp.
>
> I am trying to get the auto_increment value to be
> added in order of sequence of when the visits occurred.
>
> Thank you.
>
> Ron
>
From: "Ron Piggott" on 7 Sep 2010 12:05
I am receiving the following error Adriano:

SQL query:

ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats` ;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'CREATE TABLE `stats2` LIKE `stats`' at line 2

The complete commands were:

ALTER TABLE `stats` DROP `visits`
CREATE TABLE `stats2` LIKE `stats`;
ALTER TABLE `stats2` ADD COLUMN `visits` INT( 25 ) NOT NULL
AUTO_INCREMENT, ADD PRIMARY KEY(`visits`) FIRST;
INSERT INTO `stats2` SELECT *, 0 FROM `stats` ORDER BY `initial_access`;

I don't understand the error, your way of creating a table is new to me.
Did something small get missed?

Ron

From: Kapu on 7 Sep 2010 12:10
You are missing a semicolon after the first alter statement.

Kapu

On 7. 9. 2010 18:05, Ron Piggott wrote:
> I am receiving the following error Adriano:
>
> SQL query:
>
> ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats` ;
>
> MySQL said: Documentation
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> 'CREATE TABLE `stats2` LIKE `stats`' at line 2
>
> The complete commands were:
>
> ALTER TABLE `stats` DROP `visits`
> CREATE TABLE `stats2` LIKE `stats`;
> ALTER TABLE `stats2` ADD COLUMN `visits` INT( 25 ) NOT NULL
> AUTO_INCREMENT, ADD PRIMARY KEY(`visits`) FIRST;
> INSERT INTO `stats2` SELECT *, 0 FROM `stats` ORDER BY `initial_access`;
>
> I don't understand the error, your way of creating a table is new to me.
> Did something small get missed?
>
> Ron
>
>
From: "Adriano Rodrigo Guerreiro Laranjeira" on 8 Sep 2010 06:31
Hello!

I didn't use any DROP statements. Do exactly as following:
mysql> CREATE TABLE stats2 LIKE stats;
mysql> ALTER TABLE stats2 ADD COLUMN id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY(id);
mysql> INSERT INTO stats2 SELECT *, 0 FROM stats ORDER BY

The first line copies the structure of table stats to a new table
called stats2;
The second one, create a new field called id;
The third inserts all records from stats to stats2, in correct order.

After all, you need drop the table stats and rename the table stats2
to stats:
mysql> DROP TABLE stats;
mysql> RENAME TABLE stats2 TO stats;

Be careful, I don't know your environment.


[]'s
Adriano!
> > > > > > > > >
> On Tue, 07 Sep 2010 18:10:34 +0200
> Kapu <kapuoriginal(a)gmail.com> wrote:
> You are missing a semicolon after the first alter statement.
>
> Kapu
>
> On 7. 9. 2010 18:05, Ron Piggott wrote:
>> I am receiving the following error Adriano:
>>
>> SQL query:
>>
>> ALTER TABLE `stats` DROP `visits` CREATE TABLE `stats2` LIKE `stats`
>>;
>>
>> MySQL said: Documentation
>> #1064 - You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to use
>>near
>> 'CREATE TABLE `stats2` LIKE `stats`' at line 2
>>
>> The complete commands were:
>>
>> ALTER TABLE `stats` DROP `visits`
>> CREATE TABLE `stats2` LIKE `stats`;
>> ALTER TABLE `stats2` ADD COLUMN `visits` INT( 25 ) NOT NULL
>> AUTO_INCREMENT, ADD PRIMARY KEY(`visits`) FIRST;
>> INSERT INTO `stats2` SELECT *, 0 FROM `stats` ORDER BY
>>`initial_access`;
>>
>> I don't understand the error, your way of creating a table is new to
>>me.
>> Did something small get missed?
>>
>> Ron
>>
>>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
 | 
Pages: 1
Prev: Module positioning
Next: SELECT / ORDER BY