Re: INSERT using SELECT results
This may help you:
mysql create table locais(
- id int,
- type varchar(2),
- `desc` varchar(10));
Query OK, 0 rows affected (0.12 sec)
mysql select * from locais;
+--+--+---+
| id | type | desc |
+--+--+---+
|1 | t1 | sk|
|2 | t2 | dsk |
|3 | t1 | ddfsk |
+--+--+---+
3 rows in set (0.00 sec)
mysql select distinct type from locais;
+--+
| type |
+--+
| t1 |
| t2 |
+--+
2 rows in set (0.00 sec)
mysql create table ztipos(id int auto_increment primary key, type
varchar(2));
Query OK, 0 rows affected (0.14 sec)
mysql insert into ztipos(type) select distinct type from locais;
Query OK, 2 rows affected (0.18 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql select * from ztipos;
++--+
| id | type |
++--+
| 1 | t1 |
| 2 | t2 |
++--+
2 rows in set (0.00 sec)
mysql
I think the ztipos table definition has a problem. If you post that we may
be able to help better
Anoop
(anokun7)
On 2/18/07, Miguel Vaz [EMAIL PROTECTED] wrote:
Hi,
I have a table LOCAIS with:
id typedesc
1 t1 blah blah
2 t1 uihuih
3 t2 pokpokp
I want to list only the distinct types and create a table with
those results.
I know how to list the distinct and i thought i got the insert
right, but it doesnt work:
INSERT INTO ztipos('type') SELECT DISTINCT type FROM locais
Whats wrong with that command?
I want the result to be:
results table ZTIPOS:
id type
1 t1
2 t2
Thanks
Miguel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Thanks and best regards,
Anoop
Re: INSERT using SELECT results
INSERT INTO ztipos (type) VALUES (SELECT DISTINCT type FROM locais)
?
-afan
Miguel Vaz wrote:
Hi,
I have a table LOCAIS with:
idtypedesc
1t1blah blah
2t1uihuih
3t2pokpokp
I want to list only the distinct types and create a table with
those results.
I know how to list the distinct and i thought i got the insert
right, but it doesnt work:
INSERT INTO ztipos('type') SELECT DISTINCT type FROM locais
Whats wrong with that command?
I want the result to be:
results table ZTIPOS:
idtype
1t1
2t2
Thanks
Miguel
--
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
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
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
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
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
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
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
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
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
OK, I found the answer. CREATE TABLE ProductSums SELECT DISTINCTROW IndenturedList.NSIPartNumber, Sum(tblInvTransaction.Qty) AS SumOfQty FROM IndenturedList;
RE: INSERT INTO () SELECT...
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...
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...
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 question
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 question
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
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
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
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 DELAYED...SELECT... doesn't delay?
What analysis are you performing which causes you to conclude that nothing is being delayed? I'd probably check this by creating an insert...select which takes at least a few seconds to execute, and then have my test application log the time, make the call to MySQL, and log the time again. I suppose you could probably do something with locking instead of making the insert slow, but I'm never very confident that the database won't secretly execute my statement and delay exposing the altered table to the locking thread until it unlocks. (I'm pretty sure MySQL won't do this, but I'm superstitious.) The online docs for DELAYED do seem a bit confusing. I don't think of DELAYED in terms of its interaction with other threads-- I think MySQL actually still provides the guarantee that SQL statements will be executed in order, although some of the docs here appear to contradict that. My view on DELAYED is that it does nothing more than make the call to the database asynchronous. Instead of calling MySQL, waiting for your statement to execute, and returning, executing a DELAYED statement results in calling MySQL, returning, and letting the statement execute asynchronously with your own thread. Although the async call happens on the server side of the connection, you can get a pretty much equivalent effect by simply spawning a new thread (with its own independent connection to MySQL) and letting it send your statement for you. Clearly, this wouldn't necessarily affect the order of execution of SQL statements in the server at all; it just lets you get on with your other work while the server is working. -Rob PS: No pedantic comments about locking, synchronization, and connection ordering with respect to thread-spawning, please; it was just an example. On 29/4/02 at 2:11 pm, Emery Lapinski [EMAIL PROTECTED] wrote: Hello, I'm trying to use the DELAYED keyword with an INSERT ... SELECT statement but it seems to ignore the DELAYED. Does anyone know if this is or is not supposed to work? Exmple: session 1 session 2 --- -- create table junk (value int); create table junk2 (value int); lock table junk read; insert into junk2 values (1); insert delayed into junk values (2); insert delayed into junk select value from junk2; unlock tables; http://www.mysql.com/doc/I/N/INSERT_SELECT.html http://www.mysql.com/doc/I/N/INSERT_DELAYED.html Thanks, Emery - 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 from select
- Original Message - From: David McInnis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, March 16, 2002 3:26 PM Subject: Insert from select I think that I saw a similar query last week some time but I cannot remember the sql syntax. Is it possible to insert from a select. Something like insert into table_a from select * from table_b where blah=somevalue Thanks, David McInnis query like; insert into table_a select * from table_b where blah=somevalue Kittiphum Worachat,MT. www.hatyailab.com - 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 from select
On Saturday, 16. March 2002 09:26, David McInnis wrote: I think that I saw a similar query last week some time but I cannot remember the sql syntax. Is it possible to insert from a select. See http://www.mysql.com/doc/I/N/INSERT_SELECT.html Regards Georg - 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 and select
Hi No you can't. if you are using and AUTO_INCREMENT field as a key you could follow the INSERT with a select statement like: SELECT * FROM tablename WHERE keyfield = LAST_INSERT_ID() See the manual: LAST_INSERT_ID() http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Miscellaneous_functions Javier Gloria wrote: Hi: how i can insert and select information at the same time. first i want to insert the information relative to the client and after that i need to select the same information on the same query. that is possible _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - 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 with SELECT on same table
Thanks for the response, you are right I would get an Exception describing that the record already exists, but because I do not know which DBMS is being used (one of Oracle, MS SQL and mySQL), I can not discern if it was a 'real' error or just the fact that the record already exists. marc -Ursprüngliche Nachricht- Von: Rick Emery [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 14. November 2001 19:46 An: Votteler Marc; [EMAIL PROTECTED] Betreff: RE: INSERT with SELECT on same table If one of the table's fields is defined as UNIQUE or PRIMARY KEY, it will allow the insertion only once with that key. When a second attempt is made to insert the record with the same key, the insertion will be ignored and you may ignore the error code that results. Would that work for 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
RE: INSERT with SELECT on same table
insert-select on the same table is not allowed in MySQL. If I remember right it isn't allowed in ANSI SQL either, supposedly because it could lead to infinite loops if implemented poorly. Jon Gardiner. -Original Message- From: Votteler Marc [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 3:30 AM To: Rick Emery; [EMAIL PROTECTED] Subject: RE: INSERT with SELECT on same table Thanks for the response, you are right I would get an Exception describing that the record already exists, but because I do not know which DBMS is being used (one of Oracle, MS SQL and mySQL), I can not discern if it was a 'real' error or just the fact that the record already exists. marc -Ursprüngliche Nachricht- Von: Rick Emery [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 14. November 2001 19:46 An: Votteler Marc; [EMAIL PROTECTED] Betreff: RE: INSERT with SELECT on same table If one of the table's fields is defined as UNIQUE or PRIMARY KEY, it will allow the insertion only once with that key. When a second attempt is made to insert the record with the same key, the insertion will be ignored and you may ignore the error code that results. Would that work for 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 with SELECT on same table
insert-select on the same table is not allowed in MySQL. If I remember right it isn't allowed in ANSI SQL either, supposedly because it could lead to infinite loops if implemented poorly. Jon Gardiner. Core SQL support does not require the DBMS to support it; enhanced SQL support allows for it. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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 with SELECT on same table
If one of the table's fields is defined as UNIQUE or PRIMARY KEY, it will allow the insertion only once with that key. When a second attempt is made to insert the record with the same key, the insertion will be ignored and you may ignore the error code that results. Would that work for you? -Original Message- From: Votteler Marc [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 14, 2001 11:01 AM To: [EMAIL PROTECTED] Subject: INSERT with SELECT on same table Hi there, I am currently facing the following problem and hope someone can help me. I want to make an INSERT into a Table only if I haven't done this before. This is because the INSERT can be done by several Programs and the record should only be inserted once. I have found the following solutions (but none of them was satisfying): 1. First do a SELECT and the make an INSERT if necessary. 2. Doing the following works fine with MS SQL and Oracle but not with mySQL: insert into myTable (myCol) select distinct '' from myTable where '' not in (select myCol from myTable ); The error I get is ERROR 1066: Not unique table/alias: 'af_events' That's it. Hope someone can help me. Thanks in advance marc - 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 ??
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
