Is INSERT...SELECT atomic? [REPOST]
Does anyone know this? In the 4.0.17 and later build, the target table of an INSERT...SELECT can be the same as the SELECT table (insert into table1...select ...from table1). Does the process essentially happen in a natural lock? In other words, could the table change between the data retrieved in the select and the time the insert gets executed? We used to use something like this: Lock tables table1 write; Select @a:=max(column) from table1; Insert into table1 (column) values (@a+1); Unlock tables; Could that now be done as: Insert into table1 (column) select max(column)+1 from table1; -Allon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is INSERT...SELECT atomic?
In the 4.0.17 and later build, the target table of an INSERT...SELECT can be the same as the SELECT table (insert into table1...select ...from table1). Does the process essentially happen in a natural lock? In other words, could the table change between the data retrieved in the select and the time the insert gets executed? We used to use something like this: Lock tables table1 write; Select @a:=max(column) from table1; Insert into table1 (column) values (@a+1); Unlock tables; Could that now be done as: Insert into table1 (column) select max(column)+1 from table1; -Allon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting Found Count When Using Limit
Tough thing to do. Fortunately we thought this through for you in jTalk. Keep your eyes peeled for a formal announcement next week. -Allon On 3/21/02 11:04 AM, "Fletcher Sandbeck" <[EMAIL PROTECTED]> wrote: > I am trying to get the full found count from a query while using LIMIT to > return > just a portion of the found records. I can use two queries in a row to get > first the found count, then the found records, but that takes twice as long as > performing either query by itself. > > SELECT COUNT(*) FROM database.table WHERE query > SELECT fields FROM database.table WHERE query LIMIT 10 > > If I use "COUNT(*),fields" in the SQL statement I have to add a GROUP BY > clause. > If I GROUP BY my auto increment field then COUNT(*) always evaluates to 1. If > I > GROUP BY a constant in every row then I get one record back with the correct > total. > > Is there any way to get both the number of records found and the first set of > results using a single query? > > Thanks, > > [fletcher] > > > -- > Fletcher Sandbeck [EMAIL PROTECTED] > Lasso Product Specialist [EMAIL PROTECTED] > Blue World Communications, Inc. http://www.blueworld.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 > Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - 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: Adding Columns to table
The problem is you are not entering any returns. You can do this: mysql> create table customers -->(Customer_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -->First_Name VARCHAR(20) NOT NULL, -->LAst_Name VARCHAR(30) NOT NULL, Etc, until you get to the last: -->; That's it. On 3/20/02 2:40 PM, "Colin Faber" <[EMAIL PROTECTED]> wrote: > I suggest using one of the windows GUI clients to modify tables, rather > than a command prompt (i.e. winmysql etc). > > > alan4100 wrote: >> >> I am creating a database called Meet_a_Geek at MS DOS prompt. The customer >> table has about 30 fields..no time counting..but I noticed that I cannot go >> beyond 9 fields without ending with a semicolon. Also another problem crops >> up when I tried to recreate the customer table with the remaining fields yet >> to be created. The error message says "Customer Table existed". >> >> Is there a windows program that I can input all at once with ease? I know >> more questions are keeping coming in...Please just refer me to good tutorial >> sites so I can continue and finish this assignment. >> >> You may take a glance at my assignment below from cut and paste with dos >> editor. >> == >> == >> >> mysql> use Meet_a_Geek; >> Database changed >> mysql> create table customers (Customer_ID INT NOT NULL PRIMARY KEY >> AUTO_INCREME >> NT, First_Name VARCHAR(20) NOT NULL, LAst_Name VARCHAR(30) NOT NULL); >> Query OK, 0 rows affected (0.00 sec) >> >> mysql> show tables from Meet_a_Geek; >> +---+ >> | Tables_in_Meet_a_Geek | >> +---+ >> | customers | >> +---+ >> 1 row in set (0.01 sec) >> >> mysql> create table customers (Customer_ID INT NOT NULL PRIMARY KEY >> AUTO_INCREME >> NT, First_Name VARCHAR(20) NOT NULL, LAst_Name VARCHAR(30) NOT NULL, address >> VAR >> CHAR(50), City VARCHAR(20), State VARCHAR(2), Zip VARCHAR(20), E_Mail >> VARCHAR(20 >> ), Age INT); >> ERROR 1050: Table 'customers' already exists >> mysql> create table customers (Customer_ID INT NOT NULL PRIMARY KEY >> AUTO_INCREME >> NT, First_Name VARCHAR(20) NOT NULL, LAst_Name VARCHAR(30) NOT NULL, address >> VAR >> CHAR(50), City VARCHAR(20), State VARCHAR(2), Zip VARCHAR(20), E_Mail >> VARCHAR(20 >> ), Age INT); Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - 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: No support for multiple statements via JDBC?
Thanks Anvar- But I guess you didn't really follow this thread. We started with the statement you are suggesting because we are porting from another datasource. As it turns out, you cannot do a Insert ... Select from the same table in MySQL. It's supported in other databases, so I was surprised. The work around is to lock the table, do a select, then an insert then unlock the table. And, thanks to everyone, I got it working under JDBC. -Allon On 3/19/02 6:56 PM, "Anvar Hussain K.M." <[EMAIL PROTECTED]> wrote: > Hi Allon, > > Only one query can be sent to server at a time. But your particular case > can be handled with the single query > > Insert Into orders (Number) select Max(Number) FROM Orders; > > Anvar. > > At 04:45 PM 19/03/2002 -0800, you wrote: >> Hi All- >> >> We are using the mm driver for MySQL and multiple statements in one >> connection do not seem to be supported: >> >> I.e. >> >> SELECT @a:=MAX(Number) FROM Orders;insert into orders (Number) values (@a+1) >> >> >> The driver throws a syntax error. You can do either one of these statements >> on their own, and you can have a semicolon in the statement, but you cannot >> combine the statements. >> >> Are we missing something? >> >> How would you do a transaction or table lock without multi statement >> support? >> >> -Allon >> >> >> >> Allon Bendavid Imacination Software >> [EMAIL PROTECTED]http://www.imacination.com/ >> 805-650-8153 >> >> Visit Imacination and start selling on the Web today with Ch-Ching! >> >> >> >> - >> 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 > > Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - 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: No support for multiple statements via JDBC?
Thanks Paul for all of the clarifications
Actually what I was saying was this:
I obviously didn't assume that sticking a semi-colon between statements
creates a transaction. I was leaving out the transaction/table lock
statements but I meant to illustrate that for instance:
LOCK TABLES Orders WRITE;
SELECT @a:=MAX(Number) FROM Orders;
INSERT
INTO Orders (...)
VALUES (..., @a+1);
UNLOCK TABLES;
Traditionally this is executed in one statement.
So your translation is:
try
{
Statement s = conn.createStatement ();
s.execute ("LOCK TABLES Orders WRITE");
s.executeQuery ("SELECT @a:=MAX(Number) FROM Orders");
s.executeUpdate ("INSERT INTO Orders (...) VALUES (..., @a+1);");
s.execute ("UNLOCK TABLES");
s.close ();
}
catch (SQLException e)
{
}
Would work as expected? (Ignoring the obvious invalidity of my Insert
statement).
The lock would occur on the statements and the insert would get the value of
the select?
Thanks a lot for your help this should be a winner.
-Allon
On 3/19/02 5:40 PM, "Paul DuBois" <[EMAIL PROTECTED]> wrote:
> At 16:45 -0800 3/19/02, Allon Bendavid wrote:
>> Hi All-
>>
>> We are using the mm driver for MySQL and multiple statements in one
>> connection do not seem to be supported:
>
> No. Multiple statements in one *string* are not supported, in the MM.MySQL
> or any other API. The client-server protocol supports sending only one
> statement to the server at a time.
>
> Multiple statements per connection are supported.
>
>>
>> I.e.
>>
>> SELECT @a:=MAX(Number) FROM Orders;insert into orders (Number) values (@a+1)
>>
>>
>> The driver throws a syntax error. You can do either one of these statements
>> on their own, and you can have a semicolon in the statement, but you cannot
>> combine the statements.
>
> Putting a semicolon in the statement is wrong in any case. It may work
> by coincidence, but you'll certainly find that trying that in other APIs
> will cause big problems. Semicolons are a convention of the mysql client
> program; don't carry them into your own programming.
>
>>
>> Are we missing something?
>>
>> How would you do a transaction or table lock without multi statement
>> support?
>
> Huh?
>
> I'm curious why you'd think that the ability to stick a semicolon between
> two statements would give you a transaction. That's certainly not true
> in mysql.
>
> If you want to issue multiple statements with the same connection, then
> just invoke execute(), executeQuery(), or executeUpdate() as necessary
> while your connection is active. If you want them within a transaction,
> use your connection object to set the autocommit mode, and the commit()
> and rollback() methods.
>
> try
> {
>conn.setAutoCommit (false);
>try
>{
>Statement s = conn.createStatement ();
>// move some money from one person to the other
>s.executeUpdate ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");
>s.executeUpdate ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");
>s.close ();
>conn.commit ();
>}
>catch (SQLException e)
>{
>System.err.println ("Transaction failed, rolling back.");
>Cookbook.printErrorMessage (e);
>// empty exception handler in case rollback fails
>try { conn.rollback (); } catch (Exception e2) { }
>}
>conn.setAutoCommit (true);
> }
> catch (Exception e)
> {
>System.err.println ("Cannot perform transaction");
>Cookbook.printErrorMessage (e);
> }
>
>
>>
>> -Allon
>>
>>
>>
>> Allon Bendavid Imacination Software
>> [EMAIL PROTECTED]http://www.imacination.com/
>> 805-650-8153
>>
>> Visit Imacination and start selling on the Web today with Ch-Ching!
>>
>
Allon Bendavid Imacination Software
[EMAIL PROTECTED]http://www.imacination.com/
805-650-8153
Visit Imacination and start selling on the Web today with Ch-Ching!
-
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 ... SELECT not supported?
Thanks for the lead Jeff, maybe you can elaborate? We need to essentially do this: Insert into orders (number, comment) select max(number) + 1, 'comment' from orders where user = 'user_name'; (Not supported in MySQL) Another user suggested using a select first to get the value, then doing an insert. The only way to insure that nothing occurs between the select and an insert though is a table lock. Unfortunately multiple statements are not supported through the JDBC driver, although it says they are. So, do see how something like this could work using the last_insert_id() to get the max of the number column where a the user column contains a certain value? All help is appreciated. -Allon On 3/19/02 10:14 AM, "Jeff Kilbride" <[EMAIL PROTECTED]> wrote: > UPDATE sequence_table > SET sequence = LAST_INSERT_ID(sequence + 1) > > Further calls to LAST_INSERT_ID on the same connection will return the value > "sequence + 1". It's connection specific, so multiple connections can update > your sequence table without interfering with each other. > > There's a great example of this in Paul DuBois' book "MySQL" from New > Riders. > > --jeff > > - Original Message - > From: "Allon Bendavid" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, March 18, 2002 7:57 PM > Subject: INSERT ... SELECT not supported? > > >> Hello All- >> >> I just read that an Insert ... Select naming the same table is not > supported >> in MySQL. >> >> What is the alternative? What we need to do is simply insert the max of a >> column + 1 to create essentially a counter in a situation where an >> auto_increment counter would not work. >> >> Anyone? >> >> -Allon >> >> >> -- > -- >> Allon Bendavid Imacination > Software >> [EMAIL PROTECTED] > http://www.imacination.com/ >> 805-650-8153 >> -- > -- >> Visit Imacination and start selling on the Web today with Ch-Ching! >> -- > -- >> >> >> >> >> - >> 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 > Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - 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
No support for multiple statements via JDBC?
Hi All- We are using the mm driver for MySQL and multiple statements in one connection do not seem to be supported: I.e. SELECT @a:=MAX(Number) FROM Orders;insert into orders (Number) values (@a+1) The driver throws a syntax error. You can do either one of these statements on their own, and you can have a semicolon in the statement, but you cannot combine the statements. Are we missing something? How would you do a transaction or table lock without multi statement support? -Allon Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - 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 ... SELECT not supported?
Hello All- I just read that an Insert ... Select naming the same table is not supported in MySQL. What is the alternative? What we need to do is simply insert the max of a column + 1 to create essentially a counter in a situation where an auto_increment counter would not work. Anyone? -Allon Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - 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
