MYSQL :: LAST_INSERT_ID With Multiple INSERT Statement

Performance Between Multiple INSERT Statements Vs Single Statement With Lots Of Data
$sql1 = "INSERT INTO mytable VALUES ("zzz","xxx")";
$result = mysql_query ($sql1);
...
$sql1000 = "INSERT INTO mytable VALUES ("zzz1000","xxx1000")";
$result = mysql_query ($sql1000);
vs

$sql = "INSERT INTO mytable VALUES
("zzz","xxx"),
...
("zzz1000","xxx1000")";
$result = mysql_query ($sql);

is there any performance difference between the 2?

btw, there could 1000-5000 row inserts.

How To Use T1's ID With LAST_INSERT_ID() For An Insert ID In T2 ??
I'm trying to use the last_insert_id function in order for me to make a copy of the id in t1 and insert it in t2. By this point I'm not sure if my query is incorrect or if it is something else I should change to have this working. When I execute a submission it's register an auto_increment id as it should in t1.

If I direct after that run a SELECT LAST_INSERT_ID()query in Mysql, the result gives me a 0. Does anyone know what's wrong here? I'm using InnoDB tables with MySQL version 4.1.7 and beneth is my query.

$query= mysql_query("INSERT INTO submissions (NameInsured,..)
VALUES ('$NameInsured',..., )")
or die (mysql_error());

$query= mysql_query("INSERT INTO test SET EvaluationID=SubmissionID WHERE SubmissionID=LAST_INSERT_ID()")

Select Statement With Multiple Counts/multiple Joins Trouble
SQL
SELECT g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username, count(c.comment_id) as comment_count, count(v.video_id) as video_count, count(m.user_id) as user_count
FROM duo_groups as g
LEFT JOIN duo_group_members as m on m.group_id=g.group_id
LEFT JOIN duo_users as u on u.user_id=m.user_id
LEFT JOIN duo_videos as v ON g.group_id=v.group_id
LEFT JOIN duo_video_comments as c on v.video_id=c.video_id
GROUP BY g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username
Will return

My current problem with the above statement is with the counts. The count is accurate when only one of the counts is returned. However when more than 1 of the counts is returned the other counts (if they are not 0) will return the same number. For example look at the screenshot above. The first row, all three counts are the same but they are not accurate. They alll point to 8 because there are 8 comments, but there aren't 8 videos or 8 users. Same with the second row. There aren't 2 users only 2 videos.

Insert Multiple Rows With One Insert Stmt And Nested Select
I'm trying to insert several rows into a table using only one insert statement:

insert into component_feature values (select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_id = 1)

When I run the select statement alone, I get the result I want:

+---+---+---------------------+---+
| 3 | 1 | software_feature_id | 1 |
+---+---+---------------------+---+
| 3 | 1 | 0 | 1 |
| 3 | 1 | 1 | 1 |
| 3 | 1 | 2 | 1 |
+---+---+---------------------+---+

But when I run the complete statement I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_i' at line 1

This query is part of a PHP application I'm building. I'd rather not have to write a PHP loop to do multiple inserts.

Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful!

Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better.

I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns.

For example, I'd want this:
1-1,1-2,1-3,1-4,1-5
2-1,2-2,2-3,2-4,2-5

To become:
1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5

The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like:

SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345

This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).

How To Insert Multiple Rows With 1 Insert Query
I am having a form on the front end which has for example 3 rows each with 3 columns. The user enters data in all the 3 rows. When he hits the add button these should get in the database. What insert query would I write to add all of them together to the database? Do I need to use some procedure?

Insert Statement
"INSERT INTO messaging (MessageID, SenderID, SenderName, RecipientID, RecipientName, DateSent, MessageSubject, MessageBody) VALUES (Null, '" & m_Sender & "', '" & m_SenderName & "', '" & m_Recipient & "', '" & m_RecipientName & "', '" & m_DateSent & "', '" & m_Subject & "', '" & m_MessageBody & "');"

the above is my insert tatement that i am using in my asp application

the problem i am having is how to handle people using " and ' in their subjectline or messagebody.. is there a way to work around this issue?

INSERT Statement
I have pulled the contents of a directory and stored them in a perl array and am trying to add the array elements to a mysql database using INSERT statements in a foreach loop with the Perl DBI

I have followed the INSERT statement syntax but I get an error becuase the script is trying to use the value as the field name even though a field name is listed.

Here is the code:

Insert Statement
I am having a MSsql query as

INSERT INTO base (var_Y_name, var_X_name, par_c, mod_num, rev_num) VALUES (?, ?, ?, ?, ?); SELECT @@IDENTITY AS 'p_id'

can I use it with out any changes or do I need to change any thing..

Does @@IDENTITY in MYsql work the same as for MSSQl..

The INSERT INTO Statement
Is there a way for me to insert data on a large scale - a type of tool. example I have over 3000 phone numbers I need to import into a table. Do I need to type:

INSERT INTO table_name
VALUES (value1, value2,....)

That would be a TON of data input.

Any suggestions on a program that will generate the statement, as opposed to me typing it out?

Insert Statement
I am having a problem with the following insert statement, mysql says it is near the WHERE fuid = '$fuid' but I can't find it would you please let me know where and what the problem may be.
Thanks.

$sql = "INSERT INTO showings VALUES (overall='$overall',inside='$inside',outside='$outside',clutter='$clutter',comments = '$feedback',feedbackreceived='1',price='$price') WHERE fuid = '$fuid'";

Insert Statement
SELECT c.first, c.last, spl.with_wipes, spl.productid
FROM client c left join client_product_link cpl on c.id=cpl.clientid
left join state_product_link spl on cpl.productid=spl.productid
where spl.productid = 140

This select statement shows me all clients that have a productid of 140. How can I insert into the cpl table a new row for each client if they do not have the productid of 140. Each client has at least two rows in this table.

Insert Statement
I have this statement which gives me mysql syntax error which i don't know how to debug it.
Code:
INSERT INTO table name ( editor ) VALUES('Henry') WHERE lang='kasa'

Insert Statement
I am trying to get the following insert statement to work

CODEinsert into tablename (Username,TabName,ColumnNumber,RowNumber,HReference,HReferenceName) Values ('name','name','1','1','servernamefoldernamefoldernamefilename.xls','name')

Complicated Insert Statement
I have two tables tableOne has a fields bug_id and resolution. TableTwo has fields bug_id, version_type, and version.

I need to create an insert statement that inserts the tableOne.bug_id into tabletwo.bug_id where tableOne.resolution='DOCUMENTED'.

SQLException With Insert Statement
Does anyone see an obvious error with the following statement:

insert into client (ClientId, CompanyName, Group) values ('fsdxxx','fdsafasdf','lsdk');

For some reason it keeps reporting the following error:
java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Group) values ('fsdxxx','fdsafasdf','lsdk')'

The client table definition is as follows:

CREATE TABLE `client` (
`ClientId` varchar(6) NOT NULL default '',
`CompanyName` varchar(25) default NULL,
`Group` varchar(25) default NULL
PRIMARY KEY (`ClientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Client information(For internal Use)';

Mysql Insert Statement
I was a foxpro AP for 12 years and you get very confident when writing the select or insert statements you have to be.

I can't find any consistancy with mysql 3.23.58

Lets take an example
CODE
INSERT INTO `sledgerdet`(cno, invno, row, unit, desc, unitprice)VALUES ($mast_cno, $invno, $invrow, $unit, $description, $unitprice)

INSERT Statement All Caps For Text
Is there a command to tell Mysql to enter text in all caps that I can put in the instert statement?

for instance:

insert into table (id, text) values (Ƈ', cap_all('hello'));

something along those lines?

INSERT Statement Syntax Error
I'm trying to make a control panel for my website that lets me insert data into a table. I'm using PHP's MySQL functions to insert this data.

$sql = "INSERT INTO Challenges(status,desc,pointvalue,winnerid,title,starttime,endtime) VALUES('$status','$desc',$pointvalue,NULL,'$title','$STimeStamp',NULL)";

$result = mysql_query($sql) or die(....

I get this error message when the mysql_query() function is run.

Query: INSERT INTO Challenges(status,desc,pointvalue,winnerid,title,starttime,endtime) VALUES('open','This is a test. This is a test. This is a test.',9,NULL,'Test1','2005-03-15-10-19',NULL)
mySQL error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc,pointvalue,winnerid,title,starttime,endtime) VALUES('open'
Error code: 1064

I've wrote other insert staments similiar to this without any problems. I've compared the code for this insert with the others many times.

MySql + VB6: Pass Keyword As Fieldname Within Insert Statement
I successfully migrated from access to mysql. The original database had a column name "group"..When i try to pass/insert the value from the form into this field, in the table, i get an error.

I believe group is also a keyword/part of mysql and its causing the error. Is there a way to pass this keyword as the field? Such as wrap it in something (like brackets) to pass the info as a field without changing the column name?

for example the correct form (if it wasn't a field name) would be:
"insert into tbl (group) value('" & groupnum.text & "')"

so how can i pass this "keyword" into the mysql database as a field name?

How To Set Multiple Statement In MyODBC Connectionstring
http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-connection-parameters.html
If I want to set multiple command lines in "stmt" parameter, how to do that?

My current connection string
strConn = "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; CharSet=utf8; Port=3306; Option=0; Socket=; Stmt=SET names 'utf8' Database=yyy; Uid=xxx; Pwd=zzz;"

Multiple COUNT() In SELECT Statement
SELECT
COUNT(s01_Products.id)
FROM
s01_Products
LEFT JOIN
s01_Attributes
ON
s01_Attributes.product_id = s01_Products.id
LEFT JOIN
s01_Options
ON
s01_Options.attr_id = s01_Attributes.id
WHERE
(
s01_Products.active = 1
) AND (
(
LEFT(s01_Options.prompt,5) = "Small"
) OR (
LEFT(s01_Options.prompt,2) IN (28,30)
)
)

SELECT
COUNT(s01_Products.id)
FROM
s01_Products
LEFT JOIN
s01_Attributes
ON
s01_Attributes.product_id = s01_Products.id
LEFT JOIN
s01_Options
ON
s01_Options.attr_id = s01_Attributes.id
WHERE
(
s01_Products.active = 1
) AND (
(
LEFT(s01_Options.prompt,6) = "Medium"
) OR (
LEFT(s01_Options.prompt,2) IN (32)
)
)
And one for Large and Extra Large...

I would obviously like to combine all 4 queries in to 1 so that I can get my 4 counts in a single,

Select Statement From Multiple Tables
how can I do this type of join:

table1: id, value1, value2
table2: id, value1, value2
table3: id, value1, value2

the "id" is a product code
the values are new & used pricing

I need a select that can join all this data, and fill 0's if an id is
not listed in a gven table, then output to a file like so:

id, table1.value1, table1,value2, table2.value1, table2.value2,
table3.value1, table3.value2

obviously, if I join on all the ids, it only will output the values
that have the same id in every table. how do I get that plus all the
values that are unique to each table?

MySql Update Or Insert Statement To Modify A Word In All Caps
I have a field in my database called Subtopic. In this field I have a word LITERATURE in all caps. I need to change the word LITERATURE to LITERATURE followed by a colon then an empty space. Can someone give me the MySql statement I can paste into phpMyAdmin to make this change? Note: The Subtopic field contains the word "literature" but not in all caps because the Subtopic field is part the database that is an encyclopedia. Therefore, I want to change ONLY the word LITERATURE if the word LITERATURE is in all caps.

Multiple Insert
How can I measure the size of a mutiple insert statement in bytes?

Ex: insert into table1 (field1,field2) values ('a',111),('b',222),('c',333);

Is there a tool or a sql-command that could give me the size in bytes of the queries I'm sending to the server?

Multiple INSERT
Can i make multiple inserts into a table using one statement.
the statement:

INSERT INTO table1 (UserName) VALUES (SELECT DISTINCT UserName FROM
table2)

Basically, i want to take out all the UserNames from table2 and insert
them into table1.

Multiple Insert
the statement i am using is below
INSERT INTO City VALUES (8,'Herat','AFG','Herat',186800);
INSERT INTO City VALUES (9,'Kabul','AFG','Kabol',196800);

And the message it gives is

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
INSERT INTO City VALUES (9,'Kabul','AFG','Kabol',196800)' at line 1.

How Do I Write Multiple Conditions In An MySQL IF Statement?
I am using version 3.23 and want to write something like this:

Syntax For Multiple Table Sql Select Statement
I have 3 tables as below:

Room (roomId, noBeds)
RoomBooking (roomBookingId, bookingId, roomId)
Booking (bookingId, checkInDate, stayLengthNights)

where Room is linked to RoomBooking by roomId, and RoomBooking is linked to Booking by bookingId.

What I am trying to do is list all of the rooms available for rent between 2 inclusive dates. The statement I have sofar is below, and I understand in the first half of the statement in the where clause is not correct but I just cant figure out how to do it.

Code:

Multiple Items In An ALTER TABLE Statement
Was in the midst of doing something today and I attempted to drop a
number of columns in a table with the following:

ALTER TABLE tmp DROP COLUMN col_1, col_2, col_3, col_4;

Unfortunately MySQL gave me an error reading:

ERROR 1064: You have an error in your SQL syntax. Check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'col_2, col_3, col_4' at line 1

Can you not have multiple columns names in an alter statement?

Multiple Prepare Statement Inside The Procedure
I'm trying with the multiple dynamic queries inside the stored procedure means, inside my stored procedure i want to execute Multiple queries (DDL and DML). for that i'm storing query in variable and then executting it by prepare stmt.

It works fine with singal prepare stmt and failes for the multiple prepare stmt.

Is there any solution to execute multiple queries inside the Stored procedures or Functions?

Accessing Multiple Tables With A Select Statement
I am writing a select statement, that says something to this effect. Select * from texas, california, oklahoma; Everything in each table is going to be the exact same. as far as city, name, and zip...they are the titles of my fields. But when I go and look at this file, it just shows everyone from oklahoma. If take out out oklahoma, then it shows everyone from california but not texas. What is the proper way to do this?

How To Insert Into Multiple Tables
I have a situation where I need to insert into 2 tables that are related through ID and V3DETAILKEY.

The tables are:
V3DETAILSERVICE (ID VARCHAR(9), V3DETAILKEY VARCHAR(20), SERVICE NUMBER(7,4))

V3DETAILSALARY (ID VARCHAR(9), V3DETAILKEY VARCHAR(20), SALARY NUMBER(9,2))

So, the ID is 000001, SERVICE is 12.00, SALARY is 2200.00, V3DETAILKEY is automatically generated. Now if I was just going to insert into each table separately this is how I would do it:

INSERT INTO V3DETAILSERVICE
VALUES ('000001', V3DETAILKEY_SEQ.NEXTVAL, 12.00)

INSERT INTO V3DETAILSALARY
VALUES ('000001', V3DETAILKEY_SEQ.NEXTVAL, 2200.00)

However I need to update both tables with the same V3DETAILKEY and I am not sure how to do this.

Insert Multiple Rows
I want to insert multiples rows and use sintax
"INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);".

But, I get the following error: "Column count doesn't match value count at row 1".
The number of fields is the same, only that insert multiple rows.

How To Insert The Same Value In Multiple Rows?
i just added a new column to one of my tables. it is an int column, and is currently
assigned 'NULL' in all instances. How can i insert, for instance, the value 0 into all rows?

String: Insert Multiple
I have strings with no "
" in a text field. I need to output each string with with a "
" after every 60 characters. In other words I need to change the line length from unlimited to 60. I do not want to change the stored version of the data, just the output.

What is the best way of doing this SELECT?

Multiple INSERT Queries
I have a for loop that generates and submits a query on every itteration. The number of itterations can be can be anywhere between just a couple dozen and a few thousand.
Is there a more efficient use of MySQL than this...
PHP Code:

for () {
INSERT INTO...
}
There must be some way of building the query in the for loop and sending the entire thing out as just one query submission.

Insert Multiple Rows At Once
I was faking it a couple of years ago when I made my first database for my photography website. Now I'm updating it and I'm inserting One item at a time manually thru PHP. It's slow and tedious and I've got 467 entries to do.... I'm sure there's an easy way of doing it all at once. I'm just too much of a retard and I'm too tired to try to learn some MySQL tonight. Could someone please help me?
Here's the query that I'm ending up with. (I started at Number 100, I'm increasing the Number by 100 each time and I need to go to Number 46700.)

SQL query:
INSERT INTO `Headshots` ( `Number` , `Category` , `Name` )
VALUES (
'2800', 'Head', ''
);

Insert Into Single Row Vs Multiple Rows
My question is: is it possible to add data separated by commas into an existing row that already contains some data?

Insert Data Into Multiple Tables
Can I insert data into multiple tables at once? Basically just need to insert a number into the 'membersID' column into 10 tables, but is taking ages, so wondering if I can write a query which targets all the tables or is this impossible in SQL?

Updating Table After Doing Multiple INSERT
I'm currently trying to do the following :

INSERT INTO sometable (userID,otherstuff) select ....blah blah ORDER BY RAND() LIMIT 10;

So I do that to get 10 random entries which works fine, however is there an easy way to then say...

For each of the 10 random userID's just inserted update another-field in another table with matching userID to the ones just inserted?

Insert Multiple Identical Rows
Is it possible to insert multiple identical rows using a single query without using a loop to build this query. for example:

sqlselected = 0
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN="& websitedsn
oConn.CursorLocation = 3
strSQL = "INSERT INTO testtable (testvar) VALUES ('testinsert') "
oConn.Execute strSQL, sqlselected

I would like to create say 500 of the above rows but not use a loop to build the insert query, and not use a loop to do oRs.addnew or anything like that.. Just a simple 1 line query to add a specific amount of rows. i would eventually like to use a script variable to control how many rows to add.

Insert Data Into Multiple Tables
Is it possible to insert data into multiple tables with one insert statement?

Insert Into Single / Select From Multiple Tables
I have two tables from two databases (joomla2.jos_content and maxdev.jos_content) that I need to pull data from in order to populate a single table (joomla2.jos_magazine)

Here is what I want to do (I know this query doesn't work but you get the idea what I am trying to do)

insert joomla2.jos_magazine_articles
(joomla2.jos_magazine_articles.name,
joomla2.jos_magazine_articles.article_id,
joomla2.jos_magazine_articles.category_id,
joomla2.jos_magazine_articles.catid)

select
joomla2.jos_content.title,
joomla2.jos_content.id,
joomla2.jos_content.catid,
maxdev.jos_content.catid

How To Insert And Update Multiple Values In One Record
How do I insert and update multiple values in one record. e.g.

1. How do I insert "dogs,cats, snakes" in one record
2. If I have "dogs, cats, snakes" in one record and I want to update it and add 'pigs' to make it "dogs, cats, snakes, pigs", how do I do that?

Insert Multiple Fails Completely If Any Duplicates Exist
I've been having some problems with database performance... Several
threads are constantly attempting INSERTs of new records into a large
table - that is hundreds of thousands of records -large by my account
:-) The table has a VARCHAR field tagged as UNIQUE and inserts are
kind of slow. i'm relying on the INSERT to fail on duplicates, i'm
not performing any checking before i attempt the INSERT (i figured an
additional SELECT would slow things even more - but any advice to the
contrary would be appreciated).

While these back end threads are constantly banging away with their
INSERTS, front end users are hitting the DB with searches. so i end
up with several threads simultaneously attempting very expensive DB
operations. the result is that the front end that users see is
DREADFULLY slow.

My proposed speedup is to modify the behavior of the backend threads
so that they don't constantly attempt INSERTS of single entries, but
build up a hashtable of local entries and at some interval, attempt a
multiple insert something like:

INSERT INTO table (col1,col2,col3 VALUES (x1,y1,z1), (x2,y2,z2)....

While i do expect to take a big hit when these multiple inserts
launch, in the time between, i expect front end performance to be
significantly improved.

The problem with this situation is that the multiple insert may
contain one or more new entries which will cause duplicates on my
table's UNIQUE VARCHAR field. I would like to have duplicates ignored
on insert, but have all newly unique entries be added. Sadly, from
what i've seen, once a duplicate violation for any of the new entries
is found, the call fails, leaving my newly UNIQUE entries NOT ADDED.
Is there any way to get the call to work as i want? Doing many single
row inserts sounds like it'll be much slower (judging from what i've
read) so i'd rather not resort to that.

I don't have a ton of DB experience (which may be obvious) so if i've
overlooked something simple please let me know. If there's a better
way to improve performance other than these multiple inserts, that
would be nice to know about too. Thanks for any insight,suggestions,
etc...

Mysqldump, Entire Table In One Insert, But Multiple Lines
I'm aware of the recent mysqldump change, in that it now by default
enables some optimizations.
One of those optimizations is to use single insert statements, instead
of separate insert statements, each on their own lines.
I'm also aware of the --skip-opt / -e / --skip-extended-insert

But what I'd like is a combination of having a single insert statement,
but with each record in its own line, like so:

INSERT INTO `article` (`id`, `title`, `text`) VALUES
(1, 'test', 'this is a test'),
(2, 'test2', 'also a test');

--skip-opt or --skip-extended-insert isn't it, because that'll give me:

INSERT INTO `article` (`id`, `title`, `text`) VALUES (1, 'test', 'this
is a test');
INSERT INTO `article` (`id`, `title`, `text`) VALUES (2, 'test2', 'also
a test');

LAST_INSERT_ID()
I'm considering using the LAST_INSERT_ID() function to pull the last id for a row inserted by a user. However, I am a little concerned about this and wanted to get a little clarification.

My database will have multiple users logged in, but the connection to the database uses a single username. So when a user logs on, the connect string has a hardcoded username and password. If this is the case, is there any chance that one user will pick up the LAST_INSERT_ID() for another user?

Also, if anyone has any experience, feedback, or advice for using LAST_INSERT_ID(), please feel free to share.

Ideally, I want to hear that it pulls the last ID for the session as opposed to the user if that makes sense.