Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 04:00, Hank hes...@gmail.com wrote:
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error. Otherwise, without the single quotes, the statement
would be:
INSERT into table VALUES (,'');
which would cause a syntax error. If you include the single quotes, it
becomes:
INSERT into table VALUES ('','')
which won't cause a syntax error, but might cause some logic errors in the
database. The choice is yours.
Thanks, that is a good point. I would actually prefer errors to arise
on insert then a potentially inconsistent database or bad data. I
should definitely learn to use stored procedures, I know.
That said, I do go to great lengths to validate my data. What is an
alpha value? I do check is_numeric() and null, of course.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 07:47, Reindl Harald h.rei...@thelounge.net wrote: what ugly style - if it is not numeric and you throw it to the database you are one of the many with a sql-injection because if you are get ivalid values until there you have done no sanitize before and do not here $sql=INSERT into table VALUES ( . (int)$id . ,' . mysql_real_escape_string($val) . '); or using a abstraction-layer (simple self written class) $sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val) . '); all other things in the context of hand-written queries are all the nice one we read every day in the news and should NOT recommended because the next beginner reading this makes all the mistakes again Thanks, Reindi. I actually do something like this (simplified, in real code I use an array and a small custom function): $mysqlName=mysql_real_escape_string($name); Then, in the query I can see that all my variables start with $mysql* so I know that they have been sanitized. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
what ugly style - if it is not numeric and you throw it to the database you are one of the many with a sql-injection because if you are get ivalid values until there you have done no sanitize before and do not here It's a matter of opinion. I never said the data wasn't sanitized (it is). But sometimes calculated values or bugs in PHP code end up with a null variable field. I was just suggesting the choice between two errors -- one syntax which will generate a hard failure of the query and likely whatever page, or a soft logical error, which won't. In either case, I have error trapping to catch both types of errors and alert me to them. I prefer the errors to be logical ones and not syntax errors. $sql=INSERT into table VALUES ( . (int)$id . ,' . mysql_real_escape_string($val) . '); or using a abstraction-layer (simple self written class) $sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val) . '); I think what you posted is ugly style which makes reading the actual SQL in PHP code much harder to read and debug. The data validation should take place elsewhere long before it gets to constructing the SQL statement.
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 19.09.2011 16:55, schrieb Hank: what ugly style - if it is not numeric and you throw it to the database you are one of the many with a sql-injection because if you are get ivalid values until there you have done no sanitize before and do not here It's a matter of opinion. I never said the data wasn't sanitized (it is). But sometimes calculated values or bugs in PHP code end up with a null variable field. I was just suggesting the choice between two errors -- one syntax which will generate a hard failure of the query and likely whatever page, or a soft logical error, which won't. In either case, I have error trapping to catch both types of errors and alert me to them. I prefer the errors to be logical ones and not syntax errors. $sql=INSERT into table VALUES ( . (int)$id . ,' . mysql_real_escape_string($val) . '); or using a abstraction-layer (simple self written class) $sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val) . '); I think what you posted is ugly style which makes reading the actual SQL in PHP code much harder to read and debug. The data validation should take place elsewhere long before it gets to constructing the SQL statement. it is not because it is clear that it is sanitized instead hope and pray thousands of layers somewhere else did it - for a inline-query the best solution, if you are using a framework you will never have the insert into at this place! what i meant as ugly is that you are somewhere writing an inline-query and are not sure if it is a number or not - so it is NOT sanitized before because if you tell me it is you sanitze does not work if you get a non-integer at this point and you sanitze-method has to throw the error long before if it is really working signature.asc Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 18:11, Reindl Harald h.rei...@thelounge.net wrote:
it is not because it is clear that it is sanitized instead hope and pray
thousands of layers somewhere else did it - for a inline-query the best
solution, if you are using a framework you will never have the insert into
at this place!
what i meant as ugly is that you are somewhere writing an inline-query and
are not sure if it is a number or not - so it is NOT sanitized before
because if you tell me it is you sanitze does not work if you get a
non-integer
at this point and you sanitze-method has to throw the error long before
if it is really working
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
The resulting SQL query is easy to read, and I know that everything is
escaped. No operations are ever to be performed on $M. I need to look
into a way of making it immutable (add and read only). I could do it
with an object but I prefer an array. Actually, an array wrapped in an
object could perform the escaping itself, making me doubly sure that
some other dev didn't forget to escape while playing with the code.
By the way, I've never gotten a godd explanation about why to wrap the
variables in PHP MySQL queries with curly brackets. I don't even
remember where I picked up the habit. Does anybody here know?
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
I'm not sure I'm seeing why, in particular, you are using an array here?
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:11, Hank hes...@gmail.com wrote:
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
I'm not sure I'm seeing why, in particular, you are using an array here?
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
Furthermore, I don't want to clutter the query with
mysql_real_escape_string() all over the place. Therefore, I escape
everything before it goes into the array, so I know that all the data
in the array have been escaped. I can then use the array members in
the query.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 20.09.2011 00:39, schrieb Dotan Cohen:
On Tue, Sep 20, 2011 at 01:11, Hank hes...@gmail.com wrote:
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
I'm not sure I'm seeing why, in particular, you are using an array here?
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
Furthermore, I don't want to clutter the query with
mysql_real_escape_string() all over the place. Therefore, I escape
everything before it goes into the array, so I know that all the data
in the array have been escaped. I can then use the array members in
the query
i would use a samll class holding the db-connection with insert/update-methods
pass the whole record-array, lokk what field types are used in the table
and use intval(), doubleval() or mysql_real_escape-String
so you never write insert into inline and if the function is well desigend you
can throw the whole $_POST to it without thinikng about datatypes and ignore
automatically hidden-fields which are not used in the database
having as simple class with $db-fetch_all(), $db-insert, $db-update
has also the benefit that you can easy switch between mysql/mysqli
without the big overhead of a whole abstraction-layer and extend
this class with often used methods to make development faster
and much more stable as dealing the whole time with inline code
a basic class is written in few hours and can be extended whenever
needed - i wrote one ten years ago and heavily use it these days
as all the years
public function insert($table, array $data)
{
// so here you know where to look for fieldnames/fieldtypes
// prepare the data aray with escaping/intval()/doubleval()
// and generate finally the insert
//
// as return value use 0 on errors or the insert-id
}
signature.asc
Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
I want to be sure that all variables in the query are escaped. I don't trust myself or anyone else to do this to every variable right before the query: $someVar=mysql_real_escape_string($someVar); But you're doing exactly that right before the query anyway with: $M[username]=mysql_real_escape_string($username); You're just complicating things with the addition of an unneeded array. It seems much simpler and less cluttered to just do: $someVar=mysql_real_escape_string($someVar); before your insert. All you are doing is changing $someVar to $M[...] and then using $M[...] in the query. I really don't see the difference or benefit of using your array here. Both methods are doing exactly the same thing, except one is more convoluted. Now on the other hand, if you have several elements in the array $M to be inserted, and have a function like this to escape them all at once: for each ($M as $val) $val= mysql_real_escape_string($val); then your method starts to make more sense. -Hank
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 02:09, Hank hes...@gmail.com wrote:
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
But you're doing exactly that right before the query anyway with:
$M[username]=mysql_real_escape_string($username);
You're just complicating things with the addition of an unneeded array. It
seems much simpler and less cluttered to just do:
$someVar=mysql_real_escape_string($someVar);
before your insert. All you are doing is changing $someVar to $M[...]
and then using $M[...] in the query. I really don't see the difference or
benefit of using your array here. Both methods are doing exactly the same
thing, except one is more convoluted.
I know that this has been escaped:
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
This, I don't know if it has been escaped or not:
$query=INSERT INTO table (username) VALUES ('{$username}');
Now on the other hand, if you have several elements in the array $M to be
inserted, and have a function like this to escape them all at once:
for each ($M as $val) $val= mysql_real_escape_string($val);
then your method starts to make more sense.
I could foreach it. Or not. It doesn't matter. The point is having
known-safe variables being used in the query, which are also easy to
read.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote:
i would use a samll class holding the db-connection with insert/update-methods
pass the whole record-array, lokk what field types are used in the table
and use intval(), doubleval() or mysql_real_escape-String
so you never write insert into inline and if the function is well desigend
you
can throw the whole $_POST to it without thinikng about datatypes and ignore
automatically hidden-fields which are not used in the database
having as simple class with $db-fetch_all(), $db-insert, $db-update
has also the benefit that you can easy switch between mysql/mysqli
without the big overhead of a whole abstraction-layer and extend
this class with often used methods to make development faster
and much more stable as dealing the whole time with inline code
a basic class is written in few hours and can be extended whenever
needed - i wrote one ten years ago and heavily use it these days
as all the years
public function insert($table, array $data)
{
// so here you know where to look for fieldnames/fieldtypes
// prepare the data aray with escaping/intval()/doubleval()
// and generate finally the insert
//
// as return value use 0 on errors or the insert-id
}
You are right, using a class has many benefits. I might do that on a
future project. Thanks.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote: i would use a samll class holding the db-connection with insert/update-methods pass the whole record-array, lokk what field types are used in the table and use intval(), doubleval() or mysql_real_escape-String By the way, the database connection is include()ed from a file outside the webroot. This way if Apache is ever compromised or for whatever reason stops parsing the PHP, the resulting code returned to the browser won't have the daabase info (especially the password). -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 20.09.2011 01:23, schrieb Dotan Cohen: On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote: i would use a samll class holding the db-connection with insert/update-methods pass the whole record-array, lokk what field types are used in the table and use intval(), doubleval() or mysql_real_escape-String By the way, the database connection is include()ed from a file outside the webroot. This way if Apache is ever compromised or for whatever reason stops parsing the PHP, the resulting code returned to the browser won't have the daabase info (especially the password) if stops parsing - yes, but not relevant if it is in a include if the machine is compromised it does not matter someone could read your files can read also the include outside the docroot signature.asc Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Personally I don't use any quotes for the numeric types, and single quotes for
everything else. Ie:
UPDATE mytable SET int_field = 5 WHERE id = 3;
SELECT id FROM mytable WHERE int_field = 5;
UPDATE mytable SET varchar_field = 'Test' WHERE id = 3;
SELECT id FROM mytable WHERE varchar_field = 'Test';
UPDATE mytable SET datetime_field = '2011-09-18 00:00:00' WHERE id = 3;
If you are using PHP you may need to escape the single quotes if your php
string is in single quotes:
$query = 'UPDATE mytable SET varchar_field = \'Test\' WHERE id = 3'
But if you are doing interpolation and your string is in double quotes, you
should not need to escape:
$query = UPDATE $table_name SET varchar_field = 'Test' WHERE id = 3
Some people prefer to use back quotes on field names such as:
$query = UPDATE `mytable` SET `varchar_field` = 'Test' WHERE `id` = 3
And some people prefer to put numeric fields in quotes as well, although it is
not necessary:
UPDATE mytable SET int_field = '5' WHERE id = '3';
On 9/18/11 5:00 AM, Dotan Cohen wrote:
I am somewhat confused as to the proper way to place quotes around
arguments in INSERT and SELECT statements. I also don't see where this
is made explicit in the fine manual.
If the column is type int, is it preferable to use single, double, or
no quotes on INSERT from the mysql cli?
If the column is type int, is it preferable to use single, double, or
no quotes on SELECT from the mysql cli?
If the column is type int, is it preferable to use single, double, or
no quotes on INSERT from PHP?
If the column is type int, is it preferable to use single, double, or
no quotes on SELECT from PHP?
Is it the same for decimal and float?
If the column is type varchar, is it preferable to use single or
double quotes on INSERT from the mysql cli?
If the column is type varchar, is it preferable to use single or
double quotes on SELECT from the mysql cli?
If the column is type varchar, is it preferable to use single or
double quotes on INSERT from PHP?
If the column is type varchar, is it preferable to use single or
double quotes on SELECT from PHP?
Is it the same for text and blob?
Also, in PHP often I see code examples with the variable wrapped in
curly brackets, inside single quotes. What is the purpose of the curly
brackets? Here is such an example:
$query=INSERT INTO names (name) VALUE ('{$userName}');
If the column is type datetime, is it preferable to use single or
double quotes on INSERT from the mysql cli?
If the column is type datetime, is it preferable to use single or
double quotes on SELECT from the mysql cli?
If the column is type datetime, is it preferable to use single or
double quotes on INSERT from PHP?
If the column is type datetime, is it preferable to use single or
double quotes on SELECT from PHP?
What if I am using the NOW() function?
If the column is type set, is it preferable to use single or double
quotes on INSERT from the mysql cli?
If the column is type set, is it preferable to use single or double
quotes on SELECT from the mysql cli?
If the column is type set, is it preferable to use single or double
quotes on INSERT from PHP?
If the column is type set, is it preferable to use single or double
quotes on SELECT from PHP?
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com wrote: Personally I don't use any quotes for the numeric types, and single quotes for everything else. Ie: Thanks, Brandon. I understand then that quote type is a matter of taste. I always use double quotes in PHP and I've only recently started putting ticks around table and column names. I'll stick to your convention of no quotes around numerics and single quotes around everything else. Have a terrific week! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 12:28 PM, Dotan Cohen dotanco...@gmail.com wrote:
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com wrote:
Personally I don't use any quotes for the numeric types, and single
quotes
for everything else. Ie:
Thanks, Brandon. I understand then that quote type is a matter of
taste. I always use double quotes in PHP and I've only recently
started putting ticks around table and column names. I'll stick to
your convention of no quotes around numerics and single quotes around
everything else.
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error. Otherwise, without the single quotes, the statement
would be:
INSERT into table VALUES (,'');
which would cause a syntax error. If you include the single quotes, it
becomes:
INSERT into table VALUES ('','')
which won't cause a syntax error, but might cause some logic errors in the
database. The choice is yours.
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 19.09.2011 03:00, schrieb Hank:
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error
what ugly style - if it is not numeric and you throw it to the database
you are one of the many with a sql-injection because if you are get
ivalid values until there you have done no sanitize before and do not here
$sql=INSERT into table VALUES ( . (int)$id . ,' .
mysql_real_escape_string($val) . ');
or using a abstraction-layer (simple self written class)
$sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val)
. ');
all other things in the context of hand-written queries are all the nice one we
read every
day in the news and should NOT recommended because the next beginner reading
this makes all
the mistakes again
signature.asc
Description: OpenPGP digital signature
INSERT INTO ... SELECT not inserting all rows
Dear All I'm trying to insert a bunch of data from TableA in TableB by doing SELECT INTO TableB (fieldA, fieldB, ...) SELECT fieldA, fieldB, ... FROM TableA GROUP BY fieldA, fieldC, ... ON DUPLICATE KEY UPDATE fieldZ = VALUES(fieldZ); On my PC this works fine. But on the Server, not all rows get inserted. When I execute the Query multiple times, the following output is generated (there is the same data in the Tables on the Server and the PC): ===Server=== Run 1: Query OK, 54 rows affected (0.11 sec) Records: 864 Duplicates: 27 Warnings: 0 Run 2: Query OK, 130 rows affected (0.07 sec) Records: 864 Duplicates: 65 Warnings: 0 ===PC=== Run 1: Query OK, 558 rows affected (0.30 sec) Records: 864 Duplicates: 232 Warnings: 0 Run 2: Query OK, 0 rows affected (0.08 sec) Records: 864 Duplicates: 0 Warnings: 0 On the PC, all data is inserted and the second run doesn't affect any rows. But on the Server, this is not the case. Can someone help me to debug the problem further? Is there a known issue for the used mysql version? Do I have to tune the configuration? The strange thing is, that I didn't notice any error-message in the logs or the query window. Mysql versions used: PC: Ver 5.0.51b-community-nt for Win32 on ia32 (MySQL Community Edition (GPL)) Server: mysqld Ver 5.0.38-Ubuntu_0ubuntu1-log for pc-linux-gnu on i486 (Ubuntu 7.04 distribution) Thank you very much for any assistance and Kind regards Andreas Müller -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
spurious select ERROR 1191 when insert into ... select * is done on fulltext table
Description:
copying a table with a fulltext index via
insert into ft1 select * from ft2;
into a identical table sometimes leads to select error 1191
when concurrent select's are running.
this happens in an enviroment where the searched table is
periodically created as a temporary table and finally
copied into the searched table.
so the locking time is restricted to copying and the
searches running parallel are not locked while creating
the new search table.
the error 1191 occurs to the search selects after copying, but
only sometimes not always.
without running concurrent selects while copying,
the error seems not to happen.
the mysql database version is fresh fetched from the server:
mysql-5.0.51a-linux-i686-glibc23.tar.gz
but this error could be reproduced with v5.0.45 too.
How-To-Repeat:
script 1:
create a table with a fulltext indexed column,
fill with some data and run selects on this
table in a loop.
in this test the select result is not of interest,
only if there is a problem flagged, or a success exit code.
script t1.sh:
---
#!/bin/bash
##
## create table ft1 with one fulltext column:
##
mysql -B test EOF
drop table if exists ft1;
CREATE TABLE ft1 (
data text NOT NULL,
FULLTEXT KEY data (data)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
EOF
max=1000
##
## fill table with data:
##
i=0
while [ $i -lt $max ] ; do
i=$(($i + 1))
echo insert into ft1 values ('textstring');
done | mysql -B test
##
## loop endless selecting data.
##
## on success (regardless if there is a match) print a . on success
## or print the mysql error message.
##
while true ; do
mysql -B test -e select match (data) against ('something'), data \
from ft1 where match (data) against ('something') /dev/null
if [ $? = 0 ] ; then
echo -n .
fi
done
exit 0
---
script 2:
the second script does the copying forth and back:
- drop the temp table, create and fill it.
- lock
- empty search table and fill from temp table
- unlock
running the second script with a parameter sets the count how
many times this copying should be done.
script t2.sh:
---
#!/bin/bash
if [ $1 != ] ; then
loop=$1
else
loop=9
fi
i=0
while [ $i -lt $loop ] ; do
i=$(($i + 1))
mysql -B test EOF
drop table if exists ft2;
create table ft2 like ft1;
insert into ft2 select * from ft1;
lock table ft1 write, ft2 read;
delete from ft1;
insert into ft1 select * from ft2;
unlock tables;
select count(*) from ft1;
EOF
done
---
testing:
- run t1.sh
should start printing dots for every select:
.
[...]
- run t2.sh
t2 should print the select count(*) output for every loop run:
count(*)
1000
[...]
- as t2.sh starts, the first script starts to output errors between
the successful dots:
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
...ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching
the column list
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
...ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching
the column list
[...]
the spurious behavior can be observed by running the t2.sh copy script
manyally one copy after an other:
t2.sh 1
t2.sh 1
[...]
somtimes the output of the t1.sh looping script changes to only
errors, flagging a broken table:
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
ERROR 1191 (HY000) at line 1: Can't find FULLTEXT index matching the
column list
[...]
and then continuing with the copies:
t2.sh 1
t2.sh 1
[...]
the output of the t1.sh looping script changes
back to dots, showing the table is usable.
Fix:
a fix is not known.
a usable workaround seems to be adding a repair table quick:
insert into ft1 select * from ft2;
+ repair table ft1 quick;
unlock tables;
Submitter-Id: [EMAIL PROTECTED]
Originator:Erik Schoenfelder
Organization:
Gaertner Datensysteme GbR, E-Mail: [EMAIL PROTECTED]
38114 Braunschweig, Hamburger Str. 273a, Germany, Gesellschafter
INSERT using SELECT results
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]
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]
INSERT into select ... ON DUPLICATE KEY??
I am tying to dupilicate a set of records changing only one field. That field is part of a UNIQUE key. So far I copy the records into themselves and keep the same number of records: insert ignore into user_projects select * from user_projects where user_id=[EMAIL PROTECTED] and fac_id=FAC-0002 ON DUPLICATE KEY UPDATE FAC_ID=FAC-0003 Now in my untrained mind, this should create 4 new, identical records with only the fac_id field changed. Where am I going wrong? Thanks. Chris Jones 14 Oneida Avenue Toronto, ON M5J 2E3. Tel. 416-203-7465 Fax. 416-946-1005 -- 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]
insert into... select... duplicate key
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
Re: slow insert into select statement
Thanks for all the feedback. Here's my latest attempt: SELECT @start:=NOW(); SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); LOCK TABLES rptPricingTEST READ, rptPricingTEST a READ; CREATE TEMPORARY TABLE tmpLatestDates SELECT commodity, MAX(PricingDt) as MaxDate FROM rptPricingTEST WHERE PricingDt @date1 AND PricingDt = @date2 GROUP BY commodity; CREATE TEMPORARY TABLE tmpLatestPrices SELECT b.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpLatestDates b ON b.commodity = a.commodity AND b.MaxDate = a.PricingDt; CREATE TEMPORARY TABLE tmpPrevDates SELECT a.commodity, MAX(a.PricingDt) as PrevDate FROM rptPricingTEST a, tmpLatestPrices b WHERE a.PricingDt @date1 AND a.commodity = b.commodity GROUP BY commodity; CREATE TEMPORARY TABLE tmpPrevPrices SELECT a.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpPrevDates b ON b.commodity = a.commodity AND b.PrevDate = a.PricingDt; SELECT a.commodity, a.PricingDtas PrevDate, a.PricingAvgPrice as PrevAvg, b.PricingDtas LatestDate, b.PricingAvgPrice as LatestAvg, ((b.PricingAvgPrice - a.PricingAvgPrice) /a.PricingAvgPrice) * 100 as priceChange FROM tmpPrevPrices a, tmpLatestPrices b WHERE a.commodity = b.commodity AND a.PricingAvgPrice b.PricingAvgPrice; UNLOCK TABLES; DROP TABLE IF EXISTS tmpLatestDates, tmpPrevDates, tmpLatestPrices, tmpPrevPrices; SELECT TIMEDIFF(NOW(),@start); ++ | TIMEDIFF(NOW(),@start) | ++ | 00:00:08 | ++ 1 row in set (0.00 sec) Now I get it all executed in 8 seconds. [EMAIL PROTECTED] wrote: .. snip After reviewing your original post, I am not confident that you are answering your question with this query. You said I need to generate a report of price change for all commodity for the date interval selected by users. which to me implies a report something like: +-+-+---+--++-+-+-+ |commodity|starting date|ending date|starting price|ending price|max price|min price|avg price| +-+-+---+--++-+-+-+ I am just not sure where you are going with the query you are building. Can you provide a template of the results you want? Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine You were right about this. I was merely posting the first part of my query, as that's where my trouble began. Here's the result table that I'm looking for, where priceChange is the % increase in price for the two date interval. +---+--+-++---+-+ | commodity | PrevDate | PrevAvg | LatestDate | LatestAvg | priceChange +--+-++---+-+ The slowest part of the script is when generating this table (5.00 sec). How could I optimise it further? I don't mind creating more temp tables as long as the total execution time is much reduced. I'm using mysql 4.1.1a-alpha on win2k. Thanks, nyem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow insert into select statement
Something I have done in the past (with another DB system) was to put indexes on my temp tables. I have just gone over my copy of the MySQL docs and I don't see where that is NOT allowed so I think its fair game to try it. You have two options to do this: Create the temp tables then populate them (two statements) -or- Use ALTER TABLE your temp tables to create the indexes. I have no data about which is definitely faster although I have read many times that if you index a table AFTER filling it with data, the entire operation tends to finish quicker (especially if you have lots of data). Since you are using multiple values in your joins (when you are collecting the historical prices) lets try indexing tmpLatestDates and tmpPrevDates on both columns. Do you have an index on rptPricingTest for (commodity, PricingDt) ? That could also speed up the joins. You could also combine the collection of one set of data (I chose to do your previous months values)with your final results calculations. Maybe this will work faster SELECT @start:=NOW(); SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE tmpLatestDates SELECT commodity, MAX(PricingDt) as MaxDate FROM rptPricingTEST WHERE PricingDt @date1 AND PricingDt = @date2 GROUP BY commodity; ALTER TABLE tmpLatestDates ADD KEY (commodity, MaxDate); CREATE TEMPORARY TABLE tmpLatestPrices SELECT b.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpLatestDates b ON b.commodity = a.commodity AND b.MaxDate = a.PricingDt; ALTER TABLE tmpLatestPrices ADD KEY(commodity); CREATE TEMPORARY TABLE tmpPrevDates SELECT a.commodity, MAX(a.PricingDt) as PrevDate FROM rptPricingTEST a, tmpLatestPrices b WHERE a.PricingDt @date1 AND a.commodity = b.commodity GROUP BY commodity; ALTER TABLE tmpPrevDates ADD KEY(commodity, PrevDate); /* I eliminated one temp table and added some indexes */ SELECT a.commodity, a.PricingDtas PrevDate, a.PricingAvgPrice as PrevAvg, tlp.PricingDtas LatestDate, tlp.PricingAvgPrice as LatestAvg, ((tlp.PricingAvgPrice - a.PricingAvgPrice) /a.PricingAvgPrice) * 100 as priceChange FROM rptPricingTEST a INNER JOIN tmpPrevDates tpd ON a.commodity = tpd.commodity AND a.PricingDt = tpd.PrevDate INNER JOIN tmpLatestPrices tlp ON tlp.commodity = a.commodity; DROP TABLE IF EXISTS tmpLatestDates, tmpPrevDates, tmpLatestPrices SELECT TIMEDIFF(NOW(),@start); Now, because we are using INNER JOINS, only those commodities that actually existed in the previous month will be returned. That actually makes some sense as you cannot calculate a % change from nonexistence to some value. Let us know how this works out, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine nyem [EMAIL PROTECTED]To: [EMAIL PROTECTED] .my cc: Fax to: 05/27/2004 05:01 Subject: Re: slow insert into select statement AM Thanks for all the feedback. Here's my latest attempt: SELECT @start:=NOW(); SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); LOCK TABLES rptPricingTEST READ, rptPricingTEST a READ; CREATE TEMPORARY TABLE tmpLatestDates SELECT commodity, MAX(PricingDt) as MaxDate FROM rptPricingTEST WHERE PricingDt @date1 AND PricingDt = @date2 GROUP BY commodity; CREATE TEMPORARY TABLE tmpLatestPrices SELECT b.commodity, a.PricingDt, a.PricingHighPrice, a.PricingLowPrice, a.PricingAvgPrice FROM rptPricingTEST a INNER JOIN tmpLatestDates b ON b.commodity = a.commodity AND b.MaxDate = a.PricingDt; CREATE TEMPORARY TABLE tmpPrevDates SELECT
slow insert into select statement
I have this insert statement that took a long time to execute: INSERT INTO priceLast5 SELECT DISTINCT a.commodity, a.PricingDt FROM rptPricingTEST a WHERE a.PricingDt = ( SELECT MAX(PricingDt) FROM rptPricingTEST b WHERE b.PricingDt @date1 AND b.PricingDt = @date2 AND a.commodity = b.commodity ); Query OK, 3010 rows affected (4 min 17.37 sec) Is there any way I can modify the sql statement to speed this up? Here's the result of explain (splitted for easy reading): id select_typetable type possible_keys key -- -- - - - 1 PRIMARYa index NULL idxcommodity 2 DEPENDENT SUBQUERY b ref idxcommodity idxcommodity id key_len ref rows Extra -- --- - -- 1 25 NULL 384155 Using where; Using index 2 22 dbsdvitmp.a.commodity 52 Using where; Using index And here's the rest of the sql before the above insert: SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE priceLast5 ( commodity char(22) NOT NULL, PricingDt date NOT NULL, PRIMARY KEY (commodity), KEY idxPricingDt (PricingDt), ); LOCK TABLES rptPricingTEST a READ; The source table rptPricingTEST consists of commodity id (which is a concat of several ids), pricing date, and prices (low, hi, avg). I need to generate a report of price change for all commodity for the date interval selected by users. Since this will be a web-based report, to wait for 4 minutes for a response is unacceptable. The actual table has 2 million rows which I guess can take forever to execute. regards, nyem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow insert into select statement
If you do the math a large result set will be created. You could rewriting your query or adding more indexes to see if this speeds up the process. -Original Message- From: nyem To: [EMAIL PROTECTED] Sent: 5/26/04 2:57 AM Subject: slow insert into select statement I have this insert statement that took a long time to execute: INSERT INTO priceLast5 SELECT DISTINCT a.commodity, a.PricingDt FROM rptPricingTEST a WHERE a.PricingDt = ( SELECT MAX(PricingDt) FROM rptPricingTEST b WHERE b.PricingDt @date1 AND b.PricingDt = @date2 AND a.commodity = b.commodity ); Query OK, 3010 rows affected (4 min 17.37 sec) Is there any way I can modify the sql statement to speed this up? Here's the result of explain (splitted for easy reading): id select_typetable type possible_keys key -- -- - - - 1 PRIMARYa index NULL idxcommodity 2 DEPENDENT SUBQUERY b ref idxcommodity idxcommodity id key_len ref rows Extra -- --- - -- 1 25 NULL 384155 Using where; Using index 2 22 dbsdvitmp.a.commodity 52 Using where; Using index And here's the rest of the sql before the above insert: SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE priceLast5 ( commodity char(22) NOT NULL, PricingDt date NOT NULL, PRIMARY KEY (commodity), KEY idxPricingDt (PricingDt), ); LOCK TABLES rptPricingTEST a READ; The source table rptPricingTEST consists of commodity id (which is a concat of several ids), pricing date, and prices (low, hi, avg). I need to generate a report of price change for all commodity for the date interval selected by users. Since this will be a web-based report, to wait for 4 minutes for a response is unacceptable. The actual table has 2 million rows which I guess can take forever to execute. regards, nyem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow insert into select statement
Hi, I would start with finding out if it's the select or the insert that's taking a long time... Does the priceLast5 table have heavy indexes to build? Try running the select seperately and see how long it takes... Andrew -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday 26 May 2004 14:13 To: 'nyem '; '[EMAIL PROTECTED] ' Subject: RE: slow insert into select statement If you do the math a large result set will be created. You could rewriting your query or adding more indexes to see if this speeds up the process. -Original Message- From: nyem To: [EMAIL PROTECTED] Sent: 5/26/04 2:57 AM Subject: slow insert into select statement I have this insert statement that took a long time to execute: INSERT INTO priceLast5 SELECT DISTINCT a.commodity, a.PricingDt FROM rptPricingTEST a WHERE a.PricingDt = ( SELECT MAX(PricingDt) FROM rptPricingTEST b WHERE b.PricingDt @date1 AND b.PricingDt = @date2 AND a.commodity = b.commodity ); Query OK, 3010 rows affected (4 min 17.37 sec) Is there any way I can modify the sql statement to speed this up? Here's the result of explain (splitted for easy reading): id select_typetable type possible_keys key -- -- - - - 1 PRIMARYa index NULL idxcommodity 2 DEPENDENT SUBQUERY b ref idxcommodity idxcommodity id key_len ref rows Extra -- --- - -- 1 25 NULL 384155 Using where; Using index 2 22 dbsdvitmp.a.commodity 52 Using where; Using index And here's the rest of the sql before the above insert: SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE priceLast5 ( commodity char(22) NOT NULL, PricingDt date NOT NULL, PRIMARY KEY (commodity), KEY idxPricingDt (PricingDt), ); LOCK TABLES rptPricingTEST a READ; The source table rptPricingTEST consists of commodity id (which is a concat of several ids), pricing date, and prices (low, hi, avg). I need to generate a report of price change for all commodity for the date interval selected by users. Since this will be a web-based report, to wait for 4 minutes for a response is unacceptable. The actual table has 2 million rows which I guess can take forever to execute. regards, nyem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow insert into select statement
I would also question the sub-select in the WHERE clause. I suspect that it is being evaluated once for each row of rptPricingTEST. You could get a significant performance boost if you move the results of that select into a temp table and INNER JOIN to it. That way you calculate the MAX() date only once for the entire table and not once per row (per row, per row,...) CREATE TEMPORARY TABLE tmpLatestDates SELECT commodity, MAX(PricingDt) as MaxDate FROM rptPricingTEST WHERE PricingDt @date1 AND PricingDt = @date2 GROUP BY commodity INSERT INTO priceLast5 SELECT DISTINCT a.commodity, a.PricingDt FROM rptPricingTEST a INNER JOIN tmpLatestDates b ON b.commodity = a.commodity AND b.PricingDt = a.PricingDt (By using the INNER JOIN, I question if the DISTINCT still needed, too.) After reviewing your original post, I am not confident that you are answering your question with this query. You said I need to generate a report of price change for all commodity for the date interval selected by users. which to me implies a report something like: +-+-+---+--++-+-+-+ |commodity|starting date|ending date|starting price|ending price|max price|min price|avg price| +-+-+---+--++-+-+-+ I am just not sure where you are going with the query you are building. Can you provide a template of the results you want? Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Andrew Braithwaite To: Victor Pendleton [EMAIL PROTECTED], nyem [EMAIL PROTECTED] [EMAIL PROTECTED], [EMAIL PROTECTED] com cc: Fax to: 05/26/2004 10:28 Subject: RE: slow insert into select statement AM Hi, I would start with finding out if it's the select or the insert that's taking a long time... Does the priceLast5 table have heavy indexes to build? Try running the select seperately and see how long it takes... Andrew -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday 26 May 2004 14:13 To: 'nyem '; '[EMAIL PROTECTED] ' Subject: RE: slow insert into select statement If you do the math a large result set will be created. You could rewriting your query or adding more indexes to see if this speeds up the process. -Original Message- From: nyem To: [EMAIL PROTECTED] Sent: 5/26/04 2:57 AM Subject: slow insert into select statement I have this insert statement that took a long time to execute: INSERT INTO priceLast5 SELECT DISTINCT a.commodity, a.PricingDt FROM rptPricingTEST a WHERE a.PricingDt = ( SELECT MAX(PricingDt) FROM rptPricingTEST b WHERE b.PricingDt @date1 AND b.PricingDt = @date2 AND a.commodity = b.commodity ); Query OK, 3010 rows affected (4 min 17.37 sec) Is there any way I can modify the sql statement to speed this up? Here's the result of explain (splitted for easy reading): id select_typetable type possible_keys key -- -- - - - 1 PRIMARYa index NULL idxcommodity 2 DEPENDENT SUBQUERY b ref idxcommodity idxcommodity id key_len ref rows Extra -- --- - -- 1 25 NULL 384155 Using where; Using index 2 22 dbsdvitmp.a.commodity 52 Using where; Using index And here's the rest of the sql before the above insert: SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH); SELECT @date2:=CURDATE(); CREATE TEMPORARY TABLE priceLast5 ( commodity char(22) NOT NULL
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]
INSERT INTO ... SELECT
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? If anybody have a solution i would be very gratefull. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT INTO SELECT
Table ProductSums has not been created. Can you create this table when you insert data into it? INSERT INTO ProductSums SELECT DISTINCTROW IndenturedList.NSIPartNumber, Sum(tblInvTransaction.Qty) AS SumOfQty FROM IndenturedList;
Re: INSERT INTO SELECT
OK, I found the answer. CREATE TABLE ProductSums SELECT DISTINCTROW IndenturedList.NSIPartNumber, Sum(tblInvTransaction.Qty) AS SumOfQty FROM IndenturedList;
[Q] INSERT INTO ... SELECT
Is there a limit on the number of rows that can be inserted in this way? Ie, at what point should I start worrying about out of memory errors or something similar? So, how many rows can/should be returned by the SELECT clause? __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] INSERT INTO ... SELECT
Hello Riaan, RO Is there a limit on the number of rows that can be RO inserted in this way? Ie, at what point should I start RO worrying about out of memory errors or something RO similar? RO So, how many rows can/should be returned by the SELECT clause? I had a similar case. Difference is that I tried to read result in the array and then to work from it. Hoever ... I think that the problem is in your RAM and OS. In my case I can read in memory succes up to 3 mil records. When I tried more I received Out Of Memory, too. I looked at windos swap file and it was more than 2GB Hoever, in my case I found desicion to read and insert data record by record. Slowly, but success. -- Best regards, Krasimir_Slaveykov, 03 Ôåâðóàðè 2004 ã., 15:49:27 mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** **** *** ///| |// *** *** *** *** *** *** ** ****** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] INSERT INTO ... SELECT
Riaan Oberholzer [EMAIL PROTECTED] wrote: Is there a limit on the number of rows that can be inserted in this way? Ie, at what point should I start worrying about out of memory errors or something similar? So, how many rows can/should be returned by the SELECT clause? There is no limitation in MySQL. -- 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...
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
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
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
INSERT INTO ... SELECT not creating indexes ?
Bonjour, When I insert a lot of rows in a table from another table in MyISAM format it seems that indexes in the destination table are not updated. For example in table A I have index 1. This table contains many rows. I have table B with same structure as table A but empty. INSERT INTO TABLE B SELECT * FROM TABLE A; There if I do a select using index 1 on table B it's very long and explain on the query shows me that even if it knows about index 1 it's not using it. The solution is to do a optimize on table B but I wonder why indexes are not up to date after the insert into ... select query ? Thanks for you enlightenment :) Kinds regards, Alex. - 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: How to retrieve Insert Into Select From records effected
insert ignore into select from will skip duplicates. Karl J. Stubsjoen wrote: Hello, How can I determin what erros occur from an Insert into select from statement? Namely, I'm looking for any duplicate record errors. It is important to inform the user (in a web browser) whether or not a particular insert would fail. Must I perform a lookup first, or is there a way to capture this error, or look up a list of errors for the last SQL command. Thanks, Karl - 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
How to retrieve Insert Into Select From records effected
Hello, How can I determin what erros occur from an Insert into select from statement? Namely, I'm looking for any duplicate record errors. It is important to inform the user (in a web browser) whether or not a particular insert would fail. Must I perform a lookup first, or is there a way to capture this error, or look up a list of errors for the last SQL command. Thanks, Karl - 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
WG: problem with null values by insert into .. select ..from .. join
-Ursprüngliche Nachricht- Von: Ferdinand, Dieter Gesendet am: Donnerstag, 22. August 2002 10:56 An: '[EMAIL PROTECTED]' Betreff: problem with null values by insert into .. select ..from .. join hello, i have one problem with insert into ... select ... from .. join ... my query: insert into tkanlage(standort,nummer,name,vorname, pname,pvorname,pnachname, pnr) select if(i.rv='J',3,if(pe.standort=0 or pe.standort is null or pe.standort=3,1,pe.standort)) as standort, ^^ here is the problem, without this checked with if, the record is set to NULL i.directory_number,i.directory_name,i.directory_first_name, i.displayed_name,i.phone_book_first_name,i.displayed_name, p.directory_name from import_tk as i left join import_tk as p on p.directory_number=i.directory_number and p.users0 left join personal as pe on pe.pnr=p.directory_name and austritt'1900-1-1' where i.users=0 when a field from the join is in the fieldlist and this field is null, then the whole record is set to null. server version: 3.23.49 a bug with null values by this function is also in version 3.22.xx. here is used the last value which was in this files at last time. when i check this with if and use a initial value, than it works, but this is only a workaround. goodby Mit freundlichen Grüßen Dieter Ferdinand __ Nothelfer __ Abteilung EDV Schlossstr. 22 D - 66687 Wadern - Lockweiler Phone + 49 (6871) 60 - 1211 Mobil + 49 (178) 5238926 oder 5518414 FAX+ 49 (6871) 60 - 0 - 1211 E-mail [EMAIL PROTECTED] Internet www.nothelfer.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 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
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
INSERT DELAYED...SELECT... doesn't delay?
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
Query problem with insert into....select
hi, i have 2 tables: currencyrates +-+++ | code char(3)|| currency char(3) | | name varchar(10)|| rate double| | bcurrency char(1) || ryear varchar(4) | | || rmonth varchar(10) | +-+++ I'm have a form, from which i pick 2 value 2000 and february. I'm trying to insert fields form currency to rates, matching particular conditions in order to avoid data duplication. The rate table basically has the exchange rate for a particular currency in a particular year and a particular month. The trio currency,ryear,rmonth SHOULD be unique. My query is the following: insert into rates (currency,ryear,rmonth) select a.code, 2000, february from currency a, rates b where a.bcurrency=N and a.codeb.currency and b.ryear=2000 and b.rmonth=february _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.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
insert into select failing and read only tables
SQL SQL SQL SQL hi, i have 2 problems here. 1) i have 2 tables: currency rates +-+ ++ | code char(3)| | currency char(3) | | name varchar(10)| | rate double| | bcurrency char(1 ) | | ryear varchar(4) | | | | rmonth varchar(10) | +-+ ++ I'm have a form, from which i pick 2 value 2000 and february. I'm trying to insert fields form currency to rates, matching particular conditions in order to avoid data duplication. The rate table basically has the exchange rate for a particular currency in a particular year and a particular month. The trio currency,ryear,rmonth SHOULD be unique. My query is the following: insert into rates (currency,ryear,rmonth) select a.code, 2000, february from currency a, rates b where a.bcurrency=N and a.codeb.currency and b.ryear=2000 and b.rmonth=february The error message i get is: INSERT TABLE 'rates' isn't allowed in FROM table list. How can i solve this problem?? PS:This query works well when i don't add the condition after the first condition in the where clause. 2) i have a read only table! error message when i try to execute a insert into ...select from a program. How can i check this status of the table?? Is there a command to tell me which table is read only at a particular time? - 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
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 - 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
insert and select
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
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
insert into.. select from mult. tables
Hello, I'm relatively new to mysql and I've come across a problem while trying to insert values from multiple tables into a single, existing table. Here's the syntax that I'm using: insert into table1 (col1,col2,col3,col4) select Foo.col1, Foo.col2, Bar.col3, Bunk.col4 from table2 as Foo, table3 as Bar, table4 as Bunk where Foo.col1 = 5 and Bar.col1 = 5 and Bunk.col1 = 5; This works fine so long as the where clause is true. Now here's my problem: I used AND in the above example however, what I want is for each expression to be evaluated individually, like an OR that will not stop if the first expression is true. So, each of the source tables may or may not satisfy the condition, if they do, then the proper value is inserted into table1, if they do not, then some default value should be inserted into table1 (null). This is an if / else problem but I don't know if mysql supports anything of the sort or if I need to use some sort of script to work around this prolem. Any advice would be greatly appreciated. Thanks. Justin - 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
insert into.. select from mult. tables
Hello, I'm relatively new to mysql and I've come across a problem while trying to insert values from multiple tables into a single, existing table. Here's the syntax that I'm using: insert into table1 (col1,col2,col3,col4) select Foo.col1, Foo.col2, Bar.col3, Bunk.col4 from table2 as Foo, table3 as Bar, table4 as Bunk where Foo.col1 = 5 and Bar.col1 = 5 and Bunk.col1 = 5; This works fine so long as the where clause is true. Now here's my problem: I used AND in the above example however, what I want is for each expression to be evaluated individually, like an OR that will not stop if the first expression is true. So, each of the source tables may or may not satisfy the condition, if they do, then the proper value is inserted into table1, if they do not, then some default value should be inserted into table1 (null). This is an if / else problem but I don't know if mysql supports anything of the sort or if I need to use some sort of script to work around this prolem. Any advice would be greatly appreciated. Thanks. Justin - 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
AW: INSERT with SELECT on same table
Thanks for the response, but doing this results in : ERROR 1093: INSERT TABLE 'myTable' isn't allowed in FROM table list marc -Ursprüngliche Nachricht- Von: Nathan [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 14. November 2001 19:05 An: Votteler Marc Betreff: Re: INSERT with SELECT on same table Maybe I'm not looking at it hard enough :-) but could this work: INSERT INTO myTable (myCol) SELECT DISTINCT '' FROM myTable MT WHERE MT.myCol != '' I don't have a test table setup to try this, but maybe it will work for you? I'm not totally sure I understand the intent, so if I'm off, please explain more and I will try to help! Cheers, # Nathan - 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
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
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
INSERT INTO SELECT ??
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? The mysqlhotcopy copy the whole database or it can copy a single table? Thanks a lot. Wee - 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
Problem with INSERT INTO ... SELECT
Hello, This is the situation: Table1: ID Startnr TijdAfstand SlagDatum Opmerking CRvan CRtot PR Categorie Table2: Identical columns. What is the problem? I'm trying to append all the values from table2 to table1. I was trying this SQL query: insert into table1 select Startnr, Tijd, Afstand, Slag, Datum, Opmerking, CRvan,CRtot,PR,Categorie from table2; This doesn't work, it says Column count doesn't match value count at row 1 I understand the problem. But I can't use my ID column in the select query because both tables have an AUTO INCREMENT ID. Does someone have any suggestions how to work around this?? Thanks in advance Michiel Leegwater - 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: Problem with INSERT INTO ... SELECT
Michiel i believe you need to tell the target what fields it will have too... INSERT INTO Table1 (A, B) SELECT Table2.A, Table2.B FROM Table2 Of course you can omit the Table2. part in this trivial case. making it INSERT INTO Table1 (A, B) SELECT A, B FROM Table2 -Original Message- From: Michiel Leegwater [mailto:[EMAIL PROTECTED]] Sent: Monday, August 20, 2001 3:25 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Problem with INSERT INTO ... SELECT Hello, This is the situation: Table1: ID Startnr TijdAfstand SlagDatum Opmerking CRvan CRtot PR Categorie Table2: Identical columns. What is the problem? I'm trying to append all the values from table2 to table1. I was trying this SQL query: insert into table1 select Startnr, Tijd, Afstand, Slag, Datum, Opmerking, CRvan,CRtot,PR,Categorie from table2; This doesn't work, it says Column count doesn't match value count at row 1 I understand the problem. But I can't use my ID column in the select query because both tables have an AUTO INCREMENT ID. Does someone have any suggestions how to work around this?? Thanks in advance Michiel Leegwater - 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: Problem with INSERT INTO ... SELECT
On Mon, 20 Aug 2001, Michiel Leegwater wrote: insert into table1 select Startnr, Tijd, Afstand, Slag, Datum, Opmerking, CRvan,CRtot,PR,Categorie from table2; This doesn't work, it says Column count doesn't match value count at row 1 I understand the problem. But I can't use my ID column in the select query because both tables have an AUTO INCREMENT ID. Does someone have any suggestions how to work around this?? What if you do: SELECT NULL, Startnr, Tijd, ... FROM table2 Put NULL in place of the AUTO INCREMENT id. That might work. - 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
Lock with update,insert and select
hi all. i have a problem. i use mysql-3.23.32 on FreeBSD4.2-release my project need to update/insert some datas into mysql every 5mins. but sometims these datas are lots. so i need to reduce the update/insert time as short as possible, prevent it run over 5mins. i found out that if i use LOCK write can inprove the insert/update speed. but by this way. during the lock time, i can;t select until unlock. so... this is a serious problem to me. if there is a good solution to solve it? for example, can i do a select without caring the LOCK write? or there is anyother way to insert/update fast without Lock write? Regards chchen
