RE: insert into... select... duplicate key

[Schimmel LCpl Robert B \(GCE 2nd Intel Bn Web Master\)]

Actually, both of your solutions worked. Thanks much for the input guys.

Rob

-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Monday, September 26, 2005 12:20 AM
To: Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master)
Cc: Danny Stolle; mysql@lists.mysql.com
Subject: Re: insert into... select... duplicate key

Relevant bits of the conversation so far, with my thoughts at the end:

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> Here is the problem that I am having. I am trying to make a copy of a 
> full record in a table that has a primary key with auto-increment. The

> real problem is that I want the statement to use SELECT * so that if 
> columns ever get added to the table the statement will still work for
the
> full record. I know that I can use the information_schema to do this
in
> MySQL 5, but the server I am currently work with is MySQL 4.
Basically, I
> am looking for a way to select all of the columns in a record except
one,
> so that the auto-incrementing primary key will automatically insert
> itself. Of course, if anyone has any other suggestions for a work
around,
> that would be good, too.

Danny Stolle wrote:
> You have to use the fields in your into -statement and select
statement, 
> not including the field having the auto-numbering so if e.g. field1
has
> autonumbering ->
 >
 >  insert into table1 (field2, field3) select (field2, field3) from
table1;
 >
 > autonumbering will automatically be applied :-)

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> That is the effect that I am looking for, but exactly the method that
I 
> am trying to avoid. If I type the column names into my INSERT...
SELECT 
> and someone later adds a column to the table, I would have to go back 
> into my program and update the statement. I am looking for a way to do
it
> dynamically in order to avoid maintenance of the statement in my
program
> later.

Danny Stolle wrote:
> So you actually want to dynamically insert the records, not knowing
how 
> many fields you actually have; excluding the auto-numbering field. 
> Wouldn't it be better to use PHP or another API in which you retrieve
the
> fields and create an SQL statement using these variables and having
the
> knowledge of creating the sql-statement?

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> I am using Cold Fusion ... However, the server I am working with
> currently is MySQL 4 and I am unaware of any way to retrieve the
column
> names from a table in MySQL 4.

Danny Stolle wrote:
> 
> I am not familiar with Cold Fusion but: cant you use 'show columns
from 
> table' ?? and use the result object?
> 
> This normally works in e.g. C or PHP

That should work, but seems a lot of effort.  Another option would be to
use 
a temporary table to store the row(s) to be copied.  Assuming the 
auto_increment column is named id, it would look something like this:

   # select the row(s) to be copied into a temp table
   CREATE TEMPORARY TABLE dupe SELECT * FROM yourtable WHERE
{conditions};

   # change the id column to allow NULLs
   ALTER TABLE dupe CHANGE id id INT;

   # change the id(s) to NULL
   UPDATE dupe SET id=NULL;

   # copy the rows back to the original table
   INSERT INTO yourtable SELECT * FROM dupe;

   # clean up
   DROP TABLE dupe;

This works because inserting a row with a NULL in the auto_increment id 
column works the same as leaving the column out.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: insert into... select... duplicate key

[Michael Stassen]

Relevant bits of the conversation so far, with my thoughts at the end:

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
Here is the problem that I am having. I am trying to make a copy of a 
full record in a table that has a primary key with auto-increment. The 
real problem is that I want the statement to use SELECT * so that if 
columns ever get added to the table the statement will still work for the

full record. I know that I can use the information_schema to do this in
MySQL 5, but the server I am currently work with is MySQL 4. Basically, I
am looking for a way to select all of the columns in a record except one,
so that the auto-incrementing primary key will automatically insert
itself. Of course, if anyone has any other suggestions for a work around,
that would be good, too.


Danny Stolle wrote:
You have to use the fields in your into -statement and select statement, 
not including the field having the auto-numbering so if e.g. field1 has

autonumbering ->

>
>  insert into table1 (field2, field3) select (field2, field3) from table1;
>
> autonumbering will automatically be applied :-)

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
That is the effect that I am looking for, but exactly the method that I 
am trying to avoid. If I type the column names into my INSERT... SELECT 
and someone later adds a column to the table, I would have to go back 
into my program and update the statement. I am looking for a way to do it

dynamically in order to avoid maintenance of the statement in my program
later.


Danny Stolle wrote:
So you actually want to dynamically insert the records, not knowing how 
many fields you actually have; excluding the auto-numbering field. 
Wouldn't it be better to use PHP or another API in which you retrieve the

fields and create an SQL statement using these variables and having the
knowledge of creating the sql-statement?


Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:

I am using Cold Fusion ... However, the server I am working with
currently is MySQL 4 and I am unaware of any way to retrieve the column
names from a table in MySQL 4.


Danny Stolle wrote:


I am not familiar with Cold Fusion but: cant you use 'show columns from 
table' ?? and use the result object?


This normally works in e.g. C or PHP


That should work, but seems a lot of effort.  Another option would be to use 
a temporary table to store the row(s) to be copied.  Assuming the 
auto_increment column is named id, it would look something like this:


  # select the row(s) to be copied into a temp table
  CREATE TEMPORARY TABLE dupe SELECT * FROM yourtable WHERE {conditions};

  # change the id column to allow NULLs
  ALTER TABLE dupe CHANGE id id INT;

  # change the id(s) to NULL
  UPDATE dupe SET id=NULL;

  # copy the rows back to the original table
  INSERT INTO yourtable SELECT * FROM dupe;

  # clean up
  DROP TABLE dupe;

This works because inserting a row with a NULL in the auto_increment id 
column works the same as leaving the column out.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: insert into... select... duplicate key

[Danny Stolle]

I am not familiour with Cold Fusion but: cant you use 'show columns from 
table' ?? and use the result object?


This normally works in e.g. C or PHP

danny

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:

I am using Cold Fusion and as I stated in my original message, if I were
using MySQL 5, then I could use information_schema to retrieve the
column names in the table and do it with variables in Cold Fusion. I do
that on all my pages on the MySQL 5 servers with which I work. However,
the server I am working with currently is MySQL 4 and I am unaware of
any way to retrieve the column names from a table in MySQL 4.

Rob

Danny Stolle [mailto:[EMAIL PROTECTED] wrote:

Well I haven't realy found a select method in which you can isolate a 
field. Like a complementary method, in which you select like one field, 
but shows the fields except the field which you have used in your 
select-statement.


So you excually want to dynamically insert the records, not knowing how 
many fields you excually have; excluding the auto-numbering field. 
Wouldn't it be better to use PHP or another API in which you retrieve 
the fields and create an SQL statement using these variables and having 
the knowledge of creating the sql-statement?


You could also try to automate an export and use the load datafile to 
import the information; but then again you have to rewrite the datafile.



Best regards, Danny


Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:


That is the effect that I am looking for, but exactly the method that


I


am trying to avoid. If I type the column names into my INSERT...


SELECT


and someone later adds a column to the table, I would have to go back
into my program and update the statement. I am looking for a way to do
it dynamically in order to avoid maintenance of the statement in my
program later.

Rob


Danny Stolle [mailto:[EMAIL PROTECTED] wrote:

Hi,

I am hoping you meen this:

You have to use the fields in your into -statement and select


statement,


not including the field having the auto-numbering

so if e.g. field1 has autonumbering ->
insert into table1 (field2, field3) select (field2, field3) from


table1;


autonumbering will automatticaly be applied :-)

Danny

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:



Here is the problem that I am having. I am trying to make a copy of a
full record in a table that has a primary key with auto-increment. The
real problem is that I want the statement to use SELECT * so that if
columns ever get added to the table the statement will still work for
the full record. I know that I can use the information_schema to do


this



in MySQL 5, but the server I am currently work with is MySQL 4.
Basically, I am looking for a way to select all of the columns in a
record except one, so that the auto-incrementing primary key will
automatically insert itself. Of course, if anyone has any other
suggestions for a work around, that would be good, too.



Rob Schimmel

2d Intel bn

USMC









--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: insert into... select... duplicate key

[Schimmel LCpl Robert B \(GCE 2nd Intel Bn Web Master\)]

I am using Cold Fusion and as I stated in my original message, if I were
using MySQL 5, then I could use information_schema to retrieve the
column names in the table and do it with variables in Cold Fusion. I do
that on all my pages on the MySQL 5 servers with which I work. However,
the server I am working with currently is MySQL 4 and I am unaware of
any way to retrieve the column names from a table in MySQL 4.

Rob

Danny Stolle [mailto:[EMAIL PROTECTED] wrote:

Well I haven't realy found a select method in which you can isolate a 
field. Like a complementary method, in which you select like one field, 
but shows the fields except the field which you have used in your 
select-statement.

So you excually want to dynamically insert the records, not knowing how 
many fields you excually have; excluding the auto-numbering field. 
Wouldn't it be better to use PHP or another API in which you retrieve 
the fields and create an SQL statement using these variables and having 
the knowledge of creating the sql-statement?

You could also try to automate an export and use the load datafile to 
import the information; but then again you have to rewrite the datafile.


Best regards, Danny


Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> That is the effect that I am looking for, but exactly the method that
I
> am trying to avoid. If I type the column names into my INSERT...
SELECT
> and someone later adds a column to the table, I would have to go back
> into my program and update the statement. I am looking for a way to do
> it dynamically in order to avoid maintenance of the statement in my
> program later.
> 
> Rob
> 
> 
> Danny Stolle [mailto:[EMAIL PROTECTED] wrote:
> 
> Hi,
> 
> I am hoping you meen this:
> 
> You have to use the fields in your into -statement and select
statement,
> 
> not including the field having the auto-numbering
> 
> so if e.g. field1 has autonumbering ->
> insert into table1 (field2, field3) select (field2, field3) from
table1;
> 
> autonumbering will automatticaly be applied :-)
> 
> Danny
> 
> Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> 
>>Here is the problem that I am having. I am trying to make a copy of a
>>full record in a table that has a primary key with auto-increment. The
>>real problem is that I want the statement to use SELECT * so that if
>>columns ever get added to the table the statement will still work for
>>the full record. I know that I can use the information_schema to do
> 
> this
> 
>>in MySQL 5, but the server I am currently work with is MySQL 4.
>>Basically, I am looking for a way to select all of the columns in a
>>record except one, so that the auto-incrementing primary key will
>>automatically insert itself. Of course, if anyone has any other
>>suggestions for a work around, that would be good, too.
>>
>> 
>>
>>Rob Schimmel
>>
>>2d Intel bn
>>
>>USMC
>>
>>
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: insert into... select... duplicate key

[Danny Stolle]

Well I haven't realy found a select method in which you can isolate a 
field. Like a complementary method, in which you select like one field, 
but shows the fields except the field which you have used in your 
select-statement.


So you excually want to dynamically insert the records, not knowing how 
many fields you excually have; excluding the auto-numbering field. 
Wouldn't it be better to use PHP or another API in which you retrieve 
the fields and create an SQL statement using these variables and having 
the knowledge of creating the sql-statement?


You could also try to automate an export and use the load datafile to 
import the information; but then again you have to rewrite the datafile.



Best regards, Danny


Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:

That is the effect that I am looking for, but exactly the method that I
am trying to avoid. If I type the column names into my INSERT... SELECT
and someone later adds a column to the table, I would have to go back
into my program and update the statement. I am looking for a way to do
it dynamically in order to avoid maintenance of the statement in my
program later.

Rob


Danny Stolle [mailto:[EMAIL PROTECTED] wrote:

Hi,

I am hoping you meen this:

You have to use the fields in your into -statement and select statement,

not including the field having the auto-numbering

so if e.g. field1 has autonumbering ->
insert into table1 (field2, field3) select (field2, field3) from table1;

autonumbering will automatticaly be applied :-)

Danny

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:


Here is the problem that I am having. I am trying to make a copy of a
full record in a table that has a primary key with auto-increment. The
real problem is that I want the statement to use SELECT * so that if
columns ever get added to the table the statement will still work for
the full record. I know that I can use the information_schema to do


this


in MySQL 5, but the server I am currently work with is MySQL 4.
Basically, I am looking for a way to select all of the columns in a
record except one, so that the auto-incrementing primary key will
automatically insert itself. Of course, if anyone has any other
suggestions for a work around, that would be good, too.



Rob Schimmel

2d Intel bn

USMC







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: insert into... select... duplicate key

[Schimmel LCpl Robert B \(GCE 2nd Intel Bn Web Master\)]

That is the effect that I am looking for, but exactly the method that I
am trying to avoid. If I type the column names into my INSERT... SELECT
and someone later adds a column to the table, I would have to go back
into my program and update the statement. I am looking for a way to do
it dynamically in order to avoid maintenance of the statement in my
program later.

Rob


Danny Stolle [mailto:[EMAIL PROTECTED] wrote:

Hi,

I am hoping you meen this:

You have to use the fields in your into -statement and select statement,

not including the field having the auto-numbering

so if e.g. field1 has autonumbering ->
insert into table1 (field2, field3) select (field2, field3) from table1;

autonumbering will automatticaly be applied :-)

Danny

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:
> Here is the problem that I am having. I am trying to make a copy of a
> full record in a table that has a primary key with auto-increment. The
> real problem is that I want the statement to use SELECT * so that if
> columns ever get added to the table the statement will still work for
> the full record. I know that I can use the information_schema to do
this
> in MySQL 5, but the server I am currently work with is MySQL 4.
> Basically, I am looking for a way to select all of the columns in a
> record except one, so that the auto-incrementing primary key will
> automatically insert itself. Of course, if anyone has any other
> suggestions for a work around, that would be good, too.
> 
>  
> 
> Rob Schimmel
> 
> 2d Intel bn
> 
> USMC
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: insert into... select... duplicate key

[Danny Stolle]

Hi,

I am hoping you meen this:

You have to use the fields in your into -statement and select statement, 
not including the field having the auto-numbering


so if e.g. field1 has autonumbering ->
insert into table1 (field2, field3) select (field2, field3) from table1;

autonumbering will automatticaly be applied :-)

Danny

Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote:

Here is the problem that I am having. I am trying to make a copy of a
full record in a table that has a primary key with auto-increment. The
real problem is that I want the statement to use SELECT * so that if
columns ever get added to the table the statement will still work for
the full record. I know that I can use the information_schema to do this
in MySQL 5, but the server I am currently work with is MySQL 4.
Basically, I am looking for a way to select all of the columns in a
record except one, so that the auto-incrementing primary key will
automatically insert itself. Of course, if anyone has any other
suggestions for a work around, that would be good, too.

 


Rob Schimmel

2d Intel bn

USMC




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INSERT INTO ... SELECT

[Egor Egorov]

"Ricardo Lopes" <[EMAIL PROTECTED]> wrote:
> I have to copy some records from one table into the same table is it
> posible?
> 
> my sql is like this:
> 
> INSERT INTO equipamento_componentes (cod_equipamento_componentes, cod_tipo,
> numero, data_colocacao, cod_componente)
> 
> SELECT '', 'C', 65, NOW(), EQ.cod_componente
> 
> FROM equipamento_componentes EQ
> 
> WHERE (EQ.cod_tipo='C' and EQ.numero=1)
> 
> 
> 
> The table is like this:
> 
> cod_equipamento_componentes intauto_increment
> 
> cod_tipochar
> 
> numeroint
> 
> data_colocacaodate
> 
> cod_componenteint (FK references another
> table)
> 
> 
> 
> I get an erro saying that i cannot use that table in the select, the table
> is a InnoDB.
> 
> Can i use the same table in the insert into select? do i have to use a
> temporary table?
> 

What version do you use?
You can insert data in to the same table as you use in the  SELECT part from version 
4.0.14:
http://www.mysql.com/doc/en/INSERT_SELECT.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INSERT INTO SELECT

[Jacque Scott]

OK, I found the answer.
 
CREATE TABLE ProductSums
SELECT DISTINCTROW IndenturedList.NSIPartNumber,
Sum(tblInvTransaction.Qty) AS SumOfQty
FROM IndenturedList;

Re: INSERT INTO () SELECT...

[Jocelyn Fournier]

Hi,

Yes it works, but you can't insert and select data from the same table with
this syntax.

Regards,
  Jocelyn
- Original Message -
From: "Mirza Muharemagic" <[EMAIL PROTECTED]>
To: "Eric" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, November 22, 2002 12:30 AM
Subject: Re: INSERT INTO () SELECT...


> Hi Eric,
>
>thats oracle function INSERT INTO ... SELECT. it doesn't work in
>mysql.
>
>Mirza
>[EMAIL PROTECTED]
>
> __
> 21.11.2002 22:13
>
>
> > Hi,
>
> > This should work, I think, but doesn't
>
>
> > INSERT INTO holds (ord_num)
> > SELECT orders.ord_num FROM orders
> > LEFT JOIN holds ON orders.ord_num = holds.ord_num
> > WHERE holds.ord_num IS NULL
>
> > I have some order numbers that are not in holds that are in orders. I
want
> > to make entries in holds to match those missing.
>
> > The above SELECT gives me exactly what I want, but I get
>
> > ERROR 1066: Not unique table/alias: 'holds'
>
>
> > Thanks,
>
> > Eric
>
>
> > mysql,sql why because we love you!
>
>
>
>
>
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
>
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: INSERT INTO () SELECT...

[Mirza Muharemagic]

Hi Eric,

   thats oracle function INSERT INTO ... SELECT. it doesn't work in
   mysql.

   Mirza
   [EMAIL PROTECTED]

__
21.11.2002 22:13


> Hi,

> This should work, I think, but doesn't


> INSERT INTO holds (ord_num)
> SELECT orders.ord_num FROM orders
> LEFT JOIN holds ON orders.ord_num = holds.ord_num
> WHERE holds.ord_num IS NULL

> I have some order numbers that are not in holds that are in orders. I want 
> to make entries in holds to match those missing.

> The above SELECT gives me exactly what I want, but I get

> ERROR 1066: Not unique table/alias: 'holds'


> Thanks,

> Eric


> mysql,sql why because we love you!





> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)

> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: INSERT INTO () SELECT...

[Jennifer Goodie]

http://www.mysql.com/doc/en/INSERT_SELECT.html

You cannot select from the table you are inserting into.  Insert into a temp
table and then insert into holds from that table.

-Original Message-
From: Eric [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 21, 2002 1:14 PM
To: [EMAIL PROTECTED]
Subject: INSERT INTO () SELECT...



Hi,

This should work, I think, but doesn't


INSERT INTO holds (ord_num)
SELECT orders.ord_num FROM orders
LEFT JOIN holds ON orders.ord_num = holds.ord_num
WHERE holds.ord_num IS NULL

I have some order numbers that are not in holds that are in orders. I want
to make entries in holds to match those missing.

The above SELECT gives me exactly what I want, but I get

ERROR 1066: Not unique table/alias: 'holds'


Thanks,

Eric


mysql,sql why because we love you!





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: INSERT INTO ... SELECT question

[Paul DuBois]

At 11:15 -0600 11/14/02, Greg Macek wrote:

Thanks for the tip! Looks like I can change my date_archived field to
timestamp(8), since all I care about for this is the date information
(actual time is useless to me). My sql query all of a sudden got a lot
simpler. Thanks again for the help!


TIMESTAMP values are always stored to the full 14 digits.  The 8
affects display only, though that appears to be exactly what you want.




On Thu, 2002-11-14 at 11:07, Matthew Baranowski wrote:

 Hey Greg:

 A slightly easier way to do this is to use a "timestamp" field. Timestamp is
 just a standard mysql data type. When a record is added, it records the
 current time. When a record is updated, the timestamp field will be set to
 the time of the update.

 http://www.mysql.com/doc/en/DATETIME.html
 The TIMESTAMP column type provides a type that you can use to automatically
 mark INSERT or UPDATE operations with the current date and time. If you have
 multiple TIMESTAMP columns, only the first one is updated automatically.

 Thanks,

 Matt

 Matthew P Baranowski
 Data Manager, Office of Educational Assessment

 > University of Washington



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: INSERT INTO ... SELECT question

[Greg Macek]

Thanks for the tip! Looks like I can change my date_archived field to
timestamp(8), since all I care about for this is the date information
(actual time is useless to me). My sql query all of a sudden got a lot
simpler. Thanks again for the help!


On Thu, 2002-11-14 at 11:07, Matthew Baranowski wrote:
> Hey Greg:
> 
> A slightly easier way to do this is to use a "timestamp" field. Timestamp is
> just a standard mysql data type. When a record is added, it records the
> current time. When a record is updated, the timestamp field will be set to
> the time of the update.
> 
> http://www.mysql.com/doc/en/DATETIME.html
> The TIMESTAMP column type provides a type that you can use to automatically
> mark INSERT or UPDATE operations with the current date and time. If you have
> multiple TIMESTAMP columns, only the first one is updated automatically.
> 
> Thanks,
> 
> Matt
> 
> Matthew P Baranowski
> Data Manager, Office of Educational Assessment
> University of Washington
> 
> - Original Message -
> From: Greg Macek <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, November 14, 2002 8:06 AM
> Subject: INSERT INTO ... SELECT question
> 
> 
> > Hello,
> >
> > I recently stumbled upon the INSERT INTO..SELECT abilities. Basically
> > what I'm doing is archiving records into another table before deleting
> > them (inventory information). However, I'd like to have the archive
> > table to have one more field than the original table: a date_archived
> > function. So, for example if the tables looked like this: (quite
> > simplified)
> >
> > Original:
> >
> > carton_id
> > item_id
> > qty
> > status
> > date_recd
> >
> > Archive
> >
> > carton_id
> > item_id
> > qty
> > status
> > date_recd
> > date_archived *(new field)
> >
> > Can I have the SQL query have a NOW() in there to insert today's date
> > when running this ?
> >
> > "INSERT INTO archive
> > (carton_id,item_id,qty,status,date_recd,date_archived)
> > SELECT carton_id,item_id,qty,status,date_recd, NOW() FROM original"
> >
> > Would this work? I'd like to know upfront before basing my code around
> > this or whether or not I'll have to track how many records are going
> > into the new table and manually updating the field. Thanks.
> >
> > - Greg
> >
> >
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: INSERT INTO ... SELECT question

[Matthew Baranowski]

Hey Greg:

A slightly easier way to do this is to use a "timestamp" field. Timestamp is
just a standard mysql data type. When a record is added, it records the
current time. When a record is updated, the timestamp field will be set to
the time of the update.

http://www.mysql.com/doc/en/DATETIME.html
The TIMESTAMP column type provides a type that you can use to automatically
mark INSERT or UPDATE operations with the current date and time. If you have
multiple TIMESTAMP columns, only the first one is updated automatically.

Thanks,

Matt

Matthew P Baranowski
Data Manager, Office of Educational Assessment
University of Washington

- Original Message -
From: Greg Macek <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 14, 2002 8:06 AM
Subject: INSERT INTO ... SELECT question


> Hello,
>
> I recently stumbled upon the INSERT INTO..SELECT abilities. Basically
> what I'm doing is archiving records into another table before deleting
> them (inventory information). However, I'd like to have the archive
> table to have one more field than the original table: a date_archived
> function. So, for example if the tables looked like this: (quite
> simplified)
>
> Original:
>
> carton_id
> item_id
> qty
> status
> date_recd
>
> Archive
>
> carton_id
> item_id
> qty
> status
> date_recd
> date_archived *(new field)
>
> Can I have the SQL query have a NOW() in there to insert today's date
> when running this ?
>
> "INSERT INTO archive
> (carton_id,item_id,qty,status,date_recd,date_archived)
> SELECT carton_id,item_id,qty,status,date_recd, NOW() FROM original"
>
> Would this work? I'd like to know upfront before basing my code around
> this or whether or not I'll have to track how many records are going
> into the new table and manually updating the field. Thanks.
>
> - Greg
>
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: INSERT INTO ... SELECT question

[Greg Macek]

Well, amazingly enough, it works great! I found a test box to try it on
first before implementing this on the production box. This will
definitely make life easier... 

On Thu, 2002-11-14 at 10:14, gerald_clark wrote:
> Did you try it?
> Did it work?
> 
> Greg Macek wrote:
> 
> >Hello,
> >
> >I recently stumbled upon the INSERT INTO..SELECT abilities. Basically
> >what I'm doing is archiving records into another table before deleting
> >them (inventory information). However, I'd like to have the archive
> >table to have one more field than the original table: a date_archived
> >function. So, for example if the tables looked like this: (quite
> >simplified)
> >
> >Original:
> >
> >carton_id
> >item_id
> >qty
> >status
> >date_recd
> >
> >Archive
> >
> >carton_id
> >item_id
> >qty
> >status
> >date_recd
> >date_archived *(new field)
> >
> >Can I have the SQL query have a NOW() in there to insert today's date
> >when running this ? 
> >
> >"INSERT INTO archive
> >(carton_id,item_id,qty,status,date_recd,date_archived) 
> >SELECT carton_id,item_id,qty,status,date_recd, NOW() FROM original"
> >
> >Would this work? I'd like to know upfront before basing my code around
> >this or whether or not I'll have to track how many records are going
> >into the new table and manually updating the field. Thanks.
> >
> >- Greg
> >
> >
> >
> >
> >-
> >Before posting, please check:
> >   http://www.mysql.com/manual.php   (the manual)
> >   http://lists.mysql.com/   (the list archive)
> >
> >To request this thread, e-mail <[EMAIL PROTECTED]>
> >To unsubscribe, e-mail 
><[EMAIL PROTECTED]>
> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
> >  
> >
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: INSERT INTO ..... SELECT

[Dave]

This was resolved by adding an auto-increment column.

MySQL does not seem capable of coping with the situation below.


- Original Message -
From: "Dave" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, April 30, 2002 5:36 PM
Subject: INSERT INTO . SELECT


> I am having problems with this INSERT INTO, below is a example.  The SQL
is
> built in an ASP page and ASPVariable is, well the variable in the ASP
page.
>
> For some reason this will only insert the first row.  In my test data the
> SELECT alone returns 3 rows, but when added to the INSERT INTO only one
rows
> gets inserted.
>
> If I take out the ASPVariable and only have values from TableB in the
SELECT
> it works fine.
>
> Will MySQL not allow me to mix variables with table values with this
INSERT?
>
> Cheers
> Dave
> --
>
> INSERT INTO  TableA (ColumnA, ColumnB, ColumnC)
>
> SELECT ASPVariable, b.value1, v.value2
>
> FROM TableB b
>
> WHERE b.ValueKey = ASPVariable
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: INSERT INTO SELECT ??

[Paul DuBois]

At 12:53 PM +0800 10/3/01, chong wee siong wrote:
>Hi DBAs:
>   I want to copy TableA in DatabaseA to TableB in DatabaseB
>with changes in the attributes of the tables, what options do I have?
>
>I tried these:
>
>a) insert into TableB (id) select (id) from DatabaseA.TableA;
>this works
>b) insert into TableB (id,data) select (id,data) from DatabaseA.TableA;
>but this does not work, why? and how do I do this?

They syntax is illegal for your second attempt.  The column list for
the SELECT part shouldn't have parentheses around it:


insert into TableB (id,data) select id,data from DatabaseA.TableA;

Parentheses work in your first attempt because (id) is a simple
parenthesized expression that produces id as its value.

-- 
Paul DuBois, [EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php