SEO

1) How can I insert a column into my table DATA, and make it my
primary key (that will just be a number starting at 1 and increasing n
+1)...

2) I have a column named DATEID in a table DATA that contains the
following format connected to each other(date and name):

20071030Jones
20071020Smith
20070918Rogers
20080122Williams
etc..

What would the syntax be to insert a column to the right of this one,
and extract the first 8 digits from the data in the DATEID column and
insert that into the new column DATE, therefore making it easier for
me to query against an actual date?

Thanks

On Apr 30, 3:59 pm, F Tahbaz <ftah...@gmail.com> wrote:
> 1) How can I insert a column into my table DATA, and make it my
> primary key (that will just be a number starting at 1 and increasing n
> +1)...
>
> 2) I have a column named DATEID in a table DATA that contains the
> following format connected to each other(date and name):
>
> 20071030Jones
> 20071020Smith
> 20070918Rogers
> 20080122Williams
> etc..
>
> What would the syntax be to insert a column to the right of this one,
> and extract the first 8 digits from the data in the DATEID column and
> insert that into the new column DATE, therefore making it easier for
> me to query against an actual date?
>
> Thanks

You need something to parse your input, such as Python, PHP, C/C++,
Java, Perl, etc.

That isn't something MySQL does, or any other database for that matter.

On Thu, 01 May 2008 00:59:59 +0200, F Tahbaz <ftahbaz@gmail.com> wrote:

> 1) How can I insert a column into my table DATA, and make it my
> primary key (that will just be a number starting at 1 and increasing n
> +1)...

ALTER TABLE data ADD COLUMN id int PRIMARY KEY AUTO_INCREMENT FIRST;

> 2) I have a column named DATEID in a table DATA that contains the
> following format connected to each other(date and name):
>
> 20071030Jones
> 20071020Smith
> 20070918Rogers
> 20080122Williams
> etc..
>
> What would the syntax be to insert a column to the right of this one,
> and extract the first 8 digits from the data in the DATEID column and
> insert that into the new column DATE, therefore making it easier for
> me to query against an actual date?

IF the DATEID column is reliable:
UPDATE TABLE data ADD COLUMN datefld DATE NOT NULL AFTER dateid;
ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,9);
--
Rik Wasmus

On Thu, 01 May 2008 08:47:42 +0200, Rik Wasmus
<luiheidsgoeroe@hotmail.com> wrote:
> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,9);

Ofcourse:
ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,8));
--
Rik Wasmus

Rik Wasmus schreef:
> On Thu, 01 May 2008 08:47:42 +0200, Rik Wasmus
> <luiheidsgoeroe@hotmail.com> wrote:
>> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,9);
>
> Ofcourse:
> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,8));

Of course:
ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,1,8));

<docs http://tinyurl.com/7e7ve>
For all forms of SUBSTRING(), the position of the first character in the
string from which the substring is to be extracted is reckoned as 1.
</docs>
--
Luuk

On Thu, 01 May 2008 10:44:36 +0200, Luuk <Luuk@invalid.lan> wrote:

> Rik Wasmus schreef:
>> On Thu, 01 May 2008 08:47:42 +0200, Rik Wasmus
>> <luiheidsgoeroe@hotmail.com> wrote:
>>> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,9);
>> Ofcourse:
>> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,8));
>
> Of course:
> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,1,8));
>
> <docs http://tinyurl.com/7e7ve>
> For all forms of SUBSTRING(), the position of the first character in the
> string from which the substring is to be extracted is reckoned as 1.
> </docs>

D'oh!

.... I really hate 1-indexed anything..... Totally throws me off balance.
--
Rik Wasmus

On 1 May, 07:03, ThanksButNo <no.no.tha...@gmail.com> wrote:
> On Apr 30, 3:59 pm, F Tahbaz <ftah...@gmail.com> wrote:
>
>
>
> > 1) How can I insert a column into my table DATA, and make it my
> > primary key (that will just be a number starting at 1 and increasing n
> > +1)...
>
> > 2) I have a column named DATEID in a table DATA that contains the
> > following format connected to each other(date and name):
>
> > 20071030Jones
> > 20071020Smith
> > 20070918Rogers
> > 20080122Williams
> > etc..
>
> > What would the syntax be to insert a column to the right of this one,
> > and extract the first 8 digits from the data in the DATEID column and
> > insert that into the new column DATE, therefore making it easier for
> > me to query against an actual date?
>
> > Thanks
>
> You need something to parse your input, such as Python, PHP, C/C++,
> Java, Perl, etc.
>
> That isn't something MySQL does, or any other database for that matter.

I think you need to study the manual before making claims like this.

On May 1, 2:04 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 1 May, 07:03, ThanksButNo <no.no.tha...@gmail.com> wrote:
>
>
>
> > On Apr 30, 3:59 pm, F Tahbaz <ftah...@gmail.com> wrote:
>
> > > 1) How can I insert a column into my table DATA, and make it my
> > > primary key (that will just be a number starting at 1 and increasing n
> > > +1)...
>
> > > 2) I have a column named DATEID in a table DATA that contains the
> > > following format connected to each other(date and name):
>
> > > 20071030Jones
> > > 20071020Smith
> > > 20070918Rogers
> > > 20080122Williams
> > > etc..
>
> > > What would the syntax be to insert a column to the right of this one,
> > > and extract the first 8 digits from the data in the DATEID column and
> > > insert that into the new column DATE, therefore making it easier for
> > > me to query against an actual date?
>
> > > Thanks
>
> > You need something to parse your input, such as Python, PHP, C/C++,
> > Java, Perl, etc.
>
> > That isn't something MySQL does, or any other database for that matter.
>
> I think you need to study the manual before making claims like this.

D'OH!

Luuk <Luuk@invalid.lan> posted in comp.databases.mysql:

> Rik Wasmus schreef:
>> On Thu, 01 May 2008 08:47:42 +0200, Rik Wasmus
>> <luiheidsgoeroe@hotmail.com> wrote:
>>> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,9);
>>
>> Ofcourse:
>> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,8));
>
> Of course:
> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,1,8));

Of course:
UPDATE data SET datefld = DATE(SUBSTRING(dateid,1,8));



--
Mark A. Boyd
Keep-On-Learnin'

Mark A. Boyd wrote:
> Luuk <Luuk@invalid.lan> posted in comp.databases.mysql:
>
>> Rik Wasmus schreef:
>>> On Thu, 01 May 2008 08:47:42 +0200, Rik Wasmus
>>> <luiheidsgoeroe@hotmail.com> wrote:
>>>> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,9);
>>> Ofcourse:
>>> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,0,8));
>> Of course:
>> ÚPDATE data SET datefld = DATE(SUBSTRING(dateid,1,8));
>
> Of course:
> UPDATE data SET datefld = DATE(SUBSTRING(dateid,1,8));

Hehe, took me a while to see the difference, damned US international
keyboard....

Of course, it doesn't help that for the past 2 days here in Holland
we've had one national holiday after the next...
--
Rik Wasmus