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.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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 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.
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', '' );
Identical Rows
I'm new to SQL so I hope somebody can help me with the following: Suppose I have two tables. How can I find if the two have "identical rows"; in the sense that they are allowed to have different primary keys but in respect to all the other fields they are the same.
Deleting Identical Rows
I have a table with identical row information. I need to delete duplicates. For exaple I have a table my_posts with data: post_id post 1 first post 2 second post 2 second post 3 third post I need it to be: post_id post 1 first post 2 second post 3 third post I cannot find the way to distinguish the rows.
Count Identical Rows
I've got a log table looking like this:Code: user | action | time | | a | click | 2005-05-05 a | login | 2005-04-05 b | logout | 2005-03-02 b | click | 2005-01-25 And now I wanna display the statistics in a neat interface. But I'm having problems... is there a way to select from the above table and get rows like this:Code: user | click | login | logout | | | a | 56 | 23 | 45 b | 45 | 2 | 0 c | 12 | 32 | 32 Perhaps this is more than I could expect from MySQL?
Detect Similiar/identical Rows
I have a table where I register this info: user selection_1 selection_2 Lets's asume this records: | USER | SELECTION_1 | SELECTION_2 | | A | 1 | 2 | | B | 2 | 1 | | C | 1 | 3 | | D | 1 | 2 | I want to detect what users have made the same selection (A, B and D: it can be in the same order or not). Is there any mysql function to do this. What is your suggestion for resolving this problem?
Multiple Identical Indexes
I don't know if this belongs here but I wasn't sure where to post. We have a database the size of 400mb (mysql 4.1.16, innodb). We were having some performance issues (deadlocks thrashing the server) so we started closely examining the database structure. We noticd that there are multiple indexes for the same table on the same column for many of the tables. I'm guessing this can't help in terms of performance and resource overhead. What type of perfromance remifications will multiple, identical indexes have?
Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db. Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4. What I would like to do is take the entire row and duplicate it by the number of the quantity field. This would result in the same product 4 times instead of one product with qty of 4. Reason, I am going to serialise all the products so that they each have unique barcode from the id field. I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??
Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads and posts. when i query my threads table with a join, i need to access the users table twice to get the username of the first poster and last poster. But how? I can only figure out how to get one or the other. Is my design bad? eg SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic, username FROM DiscussionThreads, users WHERE DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT 10 .
Insert Rows
I have deleted a bunch of forum posts to test something. Now, i want to enter the data rows back into the table without deleting new content that was added after my changes. I tried REPLACE, but it didn't add the old rows back in. What syntax shoudl i use. I'm running these queries by using mysql in terminal.
Insert 300+ Rows
I need to insert roughly 300 rows of data from a file that is being exported from another system. I can get it in the following format, but can change accordingly. So the text file will look something like the following. I already have the table setup for each fields and it's just a matter of taking the text file and auto-inserting the rows into the database. Can it be done
Insert Rows Into Database
I need to insert some rows into a database, but without duplicates. I can find some SQL commands to insert the rows as long as there isn't a row like it, but I am only concerned about one column. So if I had a table with an id, first name, and last name. I would not want to insert the row 6,Jim,Smith, if 5,Bob,Smith already existed. I don't want two people with the same last name, regardless what their first name and id is. Is there an easy way to do this?
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.
Insert 4096 Rows At A Time
I need to increase a fair amount of rows to a mysql table (3.5 million to be exact). I'm doing that with a php script, but for some reason I can only insert a maximum of 4096 rows at a time. Anyone has an idea why?
Multiple Rows
I've got a whole bunch of rows to create, each with a unique key. Each row gets the same value ("New") set in the "Age" column. I've seen the INSERT INTO table (rows,) VALUES (val for row1), (val for row2), etc.But what if each row gets the same exact value?
Multiple Rows
I was wondering if it is possible to get all of the inserted id's of an auto increment column when doing multiple inserts at 1 time. For example: INSERT INTO people (fname, lname) VALUES ('john', 'smith'), ('eric', 'robinson'), ('mark', 'appley'); is it possible to retrieve all of the insert ids of those inserts instead of having to loop through each individual insert and retrieve each individual row id?
Multiple Rows
I create a table with 7 columns id Mdate col1 col2 col3 col4 col5 as above shown id is small int, mdate is date and col1 to 5 are tinyint and i want to insert the values . id is auto increment and i want mdate a day for one row. and other column set to zero what i want is to enter in single query.
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.
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.
Multiple Insert Statement
Just wanted to know whether its possible to insert multiple feilds in different tables inj one nested query There are nested queries for SELECT statement like join etc Is it possible in case of INSERT statements as well
How To Prevent INSERT On Duplicate Rows With 30 Fields
I have a MySQL table with about 30 fields. I am inserting new data, and want to be sure that there are no duplicate rows. A duplicate row would be one with ALL the 30 fields (except the auto-increment index) exactly the same. How do I do that efficiently?
Get Multiple Rows Using Subquery?
I have a query similar to the following, however i'd like to get another row from the subquerys - currency. SELECT *, (SELECT xml_result_value FROM lodging_links_allocation INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 19 WHERE lod_link_alloc_lod_id = lod_id ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_1, (SELECT xml_result_value FROM lodging_links_allocation INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 13 WHERE lod_link_alloc_lod_id = lod_id ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_2, (SELECT xml_result_value FROM lodging_links_allocation INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 12 WHERE lod_link_alloc_lod_id = lod_id ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_3 FROM ( SELECT * FROM [..snip..] GROUP BY lodging_master.lod_id ORDER BY RAND(��-12-17') ) as foo HAVING (price_from_1 > 0) && (price_from_2 > 0) && (price_from_3 > 0) LIMIT 0 , 30
Multiple Update Of 21 Rows
I have a database table which holds price ranges for various classes of hire car and various hire periods. There are 7 different hire classes and 3 different hire periods. 21 records in total. Heres an example of what the table contains: ID......HIRE PERIOD.....HIRE GROUP....PRICE 1..........14.....................a...............25.00 2..........28.....................c...............15.00 The rows are shown in an editable form, which is basically a grid of text fields. I need to figure out the most efficient way of updating all the records when the grid is edited. Do I need to perform 21 individual updates? as I'm worried that this will be too cumbersome when there are multiple users. I thought of one way which involves having a hidden field for each row, called 1,2,3 etc, each with a value of eg. 14,a,25.00 as a comma seperated list. Then I split the post values into arrays and perform various updates. Seems a bit clunky though,
Count Multiple Rows
I have a table that tracks dealer transactions. The fields are Date, Dealership, Amount, A, D, W, F. A=Approved D=Denied, W=Withdrawn and F=Funded. I have made it where if a loan has been approved then A would =1 and D W F would be null. Same goes if the record was withdrawn W would =1 and the other would be null. So here is my problem: I want to group by dealership and then count how many were approved, denied, withdrawn, and funded. i am running version 3.23.58. After doing much research I either need to do unions or subqueries. However, both are not availble until 4.x. Is it possible to do what I am looking to do without upgrading?
Update Multiple Rows
I have about 20 rows that need to updated together and would rather not have 20 seperate update lines but am not sure what else to do. At the moment it looks something like this: UPDATE movies SET mon='10pm', tues='10pm', wed='11pm' WHERE movie_id='19'... UPDATE movies SET mon='9pm', tues='11pm', wed='4pm' WHERE movie_id='37'... UPDATE movies SET mon='8am', tues='1pm', wed='5pm' WHERE movie_id='19'... There is no logical order to the movie_id's they are selected from the movies table using specific criteria.
Collapsing Multiple Rows Into One.
I'm setting up a database of links, where each one can be in multiple categories. I could make each category id a column in the links table, but I'd rather store it in a separate table so I don't have a bunch of NULLs cluttering up the main table and also to allow unlimited categories. That table has two columns, linkid and categoryid. The problem is that I'd like to retrieve it as one row somehow, something like this: "linkid category1 category2 etc..."
Updating Multiple Rows
The following UPDATE query works fine if run directly into phpMyAdmin (I take the $sql output of the script with the data in it and paste it into phpMyAdmin). But it doesn't update my records if run from the PHP script. I can't seem to figure out where the bug is: Code:
Selecting From Multiple Rows??
i have made a voting script... every time someone votes it goes into a table called 'voting'... since people can vote on multiple things in the site there are multiple instances of each user in the 'voting' database... if each entry has a 'userID' a 'ratingGiven' and a 'objectBeingVotedOn' field how would i go about grabbing say the "ratingGiven" field from all of one particular user's entries .
Updating Multiple Rows
I need to update multipule rows a once, using PHP. Here's what I mean. I have a mysql query out put the data as a form like this: Item 1 <input type="hidden" name="id" value="1"> <input type="text" name="order" value=""> Item 2 <input type="hidden" name="id" value="3"> <input type="text" name="order" value=""> Item 3 <input type="hidden" name="id" value="4"> <input type="text" name="order" value=""> <input type="submit"> Order need to be updated. I could setup to do a loop with multiple queries.
Update Multiple Rows
I have an array that I need to put into an existing db column using a perl foreach loop. I can do this using an INSERT statement but the script will be run every day and the source array will be changing as it is based on directory contents so I need to UPDATE vs. INSERT With the INSERT statment the script places each array element on a new row of the db as it should but when I try to use an UPDATE statement, I get a single array element written over and over on each row of the db. Code:
How To Evaluate Multiple Rows As One Using IF( )
Each record for a PhysicalSource has a Status number. I need to do a SELECT statement to find out if there is a Status = 2 in any of the records. If Status = 2 then I need to return all rows for this PhysicalSource else if Status != 2 then I need to exit the query (don’t return anything). The statement is as follows: SELECT SourceID, OriginalTime, State FROM sources WHERE SourceID = ? AND PhysicalSource = ? AND IF(Status = 2, 0, 1) Order by SourceID ASC As I found out the “IF(Status = 2, 0, 1)� evaluates every row and returns all others with Status !=2. The options are limited, I inherited a GUI (can’t modify it) that must be use and only accepts one query at a time using MySQL version 4.1.11. Could someone help with a way to have this evaluate multiple rows, as one, and if in any of them Status = 2 then exit the query?
Matching Multiple Rows
I've got the following tables with the following columns: property: id, address, number_of_bedrooms attribute: id, title (e.g. washing machine, central heating, furnished) property_attribute: property, attribute (composite primary key on both id columns) what i'm trying to do is select property ids that have all the attributes i'm looking for, for example, all those properties that have a washing machine and are furnished. I've been trying things like the following which aren't working, probably because the attribute column won't be equal to two different values at once. SELECT pa.property FROM property_attribute AS pa INNER JOIN attribute AS a ON pa.attribute=a.id WHERE a.title='washing machine' AND a.title='furnished' this is returning an empty set because, i think, a.title can't have two values at once. what i want to say in pseudo-sql is: SELECT pa.property FROM property_attribute AS pa INNER JOIN attribute AS a ON pa.attribute=a.id WHERE (pa.property=X AND a.title='washing machine') AND (pa.property=X AND a.title='furnished') so the only results that will be returned are where a property has both attributes. i can't think of how to do this since there will be varying numbers of attributes i need to search on. perhaps i need to use a variable for pa.property - i tried that, setting it to SELECT DISTINCT property FROM property_attribute, but mysql didn't like it because this query returned multiple rows. i may need some kind of set variable or an array.
Select Multiple Rows With The Same Id
So I have this table: table_name id_one ---- id_two 1 ---------- 1 1 ---------- 2 1 ---------- 3 2 ---------- 1 2 ---------- 3 I want to select all of the id_one rows where the id_two is '1' and '2'. The desired output is: id_one 1 1 Later I might use SELECT DISTINCT to produce the following: id_one 1 So far I have: SELECT id_one FROM table_name WHERE id_two IN ('1','2') But this selects all the id_one rows where id_two is '1' OR '2'. I want the rows where it is '1' AND '2'.
Delete Multiple Rows At Once
My question is, what code would i use to delete multiple rows of data at the same time. if i had: username password userid john fubar 13402 mike yippy 13679 and i wanted to erase both users in one query, what would it be?
Subtracting From Multiple Rows.
I'm wondering if it's possible to subtract a single value from multiple rows. For example, say I have two rows containing pieces of an account balance, which add up to a total balance. We'll say the two rows hold balances of $10 and $20. Now I want to subtract $25 from them (meaning subtract $20 from one and $5 from the other). Is there a way to write this in one query? If I use the SUM() command can I subtract from the total instead of each unique row?
Updating Multiple Rows
I am running the following UPDATE statement UPDATE history set t_own = t_user * 0.75 where user_cur = 7 and lg = 'Local' t_own and t_user are defined as DECIMAL 12,2 650 rows should be updated, but i get error 1136 Column count doesn't match value count at row 1.
WHERE Accross Multiple Rows
Suppose you have a table: Code: attribute_id file_id attribute_value 1 81 11076 2 81 BONAP 3 81 2 4 81 2004-09-09T00:00:00 5 81 JIM01 6 81 Bon App 7 81 Margaret Peacock 1 86 11072 2 86 ERNSH 3 86 2 4 86 2004-09-01T00:00:00 5 86 PO7859 6 86 Ernst Handel 7 86 Margaret Peacock How do you find the file_id that has attribute_id = 1 and attribute_value = '11072' and attribute_id = 2 and attribute_value = 'ERNSH'?
Updating Multiple Rows.
I went to MySQL home page and found one comment that kind of tried to explain it but it was written in such a manner that I could make no sense of it at all. I am trying to find a way to update a row with 12 entries in a table that has 12 teams (teamid) and 12 ranks (r1,r2,r3...r12) the data to update the teamid comes from a form and they would all be updated (unless everything remains the same) with the use of the said form. I am not certain how to make this work (call the table 'team') PHP Code: UPDATE team SET teamid='team1', teamid='team2'...teamid='team12' WHERE?
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?
LAST_INSERT_ID With Multiple INSERT Statement
i have the following question: When inserting many entries into a table with 1 auto_increment key-attibute (say attribute user_id) and one data attribute (say attribute name) CREATE users(user_id int key auto_increment, name text) like INSERT INTO users(name) VALUES ('pete'),('josh'),('carl') When the inserting is done in a concurrent way (say 5 scripts do inserting operations like the one above) how do I get the autoincremented ids for all the inserted rows ? I know that LAST_INSERT_ID() yields the autoinc id of the first inserted row (of the multi-insert statement) say 1002 for 'pete' in this case and is also concurrent-safe. But is the insert statement atomic that i can assume that the subsequent generated ids for 'josh' and 'carl' are consecutive i.e. 'josh' gets 1003 and 'carl' gets 1004 when many scripts insert into this table ? I dont want to use a subsequent SELECT statement to fetch the auto_incremented ids.
|
TRACKER
