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?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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).
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
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.
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.
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', '' );
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 A ' (single Quote) Into A Set Value
I have a db of music. Columns include artist, title, genre, duration etc.. For the genre column i used a SET type and i've included things like pop, rock, rap,.... etc etc. I have several functions for manipulating the data and i've gotten quite familiar with working with sets. THe Problem: I can't get 80's to be a genre (or category for that matter) no matter what i try.. I'm using php and i've tried every combination i can think of for escaping the ' but it wont work. I cant get it to work correctly with phpmyadmin either when i manually put in the a value with a single quote.. It just won't work.. I also tried mysqlAdministrator with the same results.. I've pretty much given up on it and i instead made a new table that holds all possible values.. However i still am a bit curious, is it possible, and if so, how?
Multiple Records Single Value
I have been developing a realtor intranet system. I am currently writing a php script that deals with 2 tables "property" and "photos" property table: id | address | etc.. ---------------------------- 1 56 My Road 2 389 Your Street photo table: id | file_id | default | property_id --------------------------------------- 1 3434... 0 1 2 343c... 1 1 3 3udu... 0 1 I have added a field "default" in the "photos" table. The purpose of this is to set the default photo for the property listing. My problem is: I want to perform a single update query as I currently only know how to perform this operation with two. eg: $sql1 = "UPDATE `photos` SET `default` = 0 WHERE `property_id` LIKE {$var}"; $sql2 = "UPDATE `photos` SET `default` = 1 WHERE `file_id` LIKE {$var}"; Any way of doing the above 2 queries in a single query?
Store Multiple Value In Single Column
I have a column which need to store multiple time value,like 11:00am,2:00pm,5:30pm.How can that single column fix it?And how to control time format in between 12-hours and 24-hours format?
Multiple COUNTs Against A Single Table
I want to get some counts from a single database table and I can't figure out how (or if) I can do it in one statement. I have a table of private messages and would like to get counts on new, read and trashed messages for a user. The table: pms ---- msgID (int) (idx) toUserID (int) fromUserID (int) msgSubject (varchar) msgBody (text) msgRead (enum 'y','n') msgTrashed (enum 'y','n') I would love to do know if it can be done without using multiple separate COUNT queries. Any way you can do this with one query? Right now I'm using 3 SELECT queries which all check against the toUserID... new: SELECT COUNT(*) FROM pms WHERE toUserID=1 AND msgRead='N' AND msgTrashed='N' read: SELECT COUNT(*) FROM pms WHERE toUserID=1 AND msgRead='Y' AND msgTrashed='N' trashed: SELECT COUNT(*) FROM pms WHERE toUserID=1 AND msgTrashed='Y'
Multiple Sums In A Single SELECT
I must have a single select execute 2 sums, from two different tables. For example: SELECT tbl1.ID as ID, SUM(tbl1.value1) AS Money_Earned, SUM(tbl2.value2) AS Money_Spent FROM income tbl1, expenses tbl2 WHERE tbl1.ID = tbl2.ID GROUP BY tbl1.ID I get a table back in the form I expect, but instead of a sum, it seems I'm getting a product of the number of entries in the table that meet the criteria * the sum of those values (value1 for example in the first sum statement). In other words, if my table income table has 2 entries for ID = 1, and each value has a 5 and a 6 respectively, the result I'll get back is not 11, but rather 22.
Multiple Values In Single Field
If there is an existing field with values in it (1,2,5,6) comma deliminated, what is the process for querying and pulling these out? Is there something that would be combined with IN that would convert the values somehow?Thank you for any thoughts, and please rest assured that I am aware of the ill nature of multiple values in a single field.
Multiple Servers On A Single Host Question
I have a single instance of MySQL package installed on FreeBSD. I run 2 servers. The second server is run with this command: shell> mysqld_safe --defaults-extra-file=/root/my2.cnf & some /root/my2.cnf file entries: port = 3307 socket = /tmp/mysql2.sock Both servers co-exist peacefully except: shell> mysql --socket=/tmp/mysql2.sock [accesses second server happily] ***BUT*** shell> mysql --port=3307 [accesses the first server and not the second!!] Does that mean that the first server's port 3306 is hard-coded into MySQL ? Is this a bug ? Or am I missing something ?
Is It Possible To Sum The Values Of Multiple Fields On A Single SQL Record?
I have fields for Goals and Assists. I want to take those 2 fields and sum them to make a 3rd field PTS, then send them to variable in my PHP code to be printed to the screen. My statement is this: SELECT player_stats.G, player_stats.A, SUM (player_stats.G + player_stats.A) PTS FROM player_stats WHERE player_stats.PID =10 AND player_stats.season =1 The statement breaks with the bolded part.
Creating A Single Field From Multiple Row Results
I know how to combine multiple columns to get a single result field but I'm not sure how to combine rows from the same column into a single result field. Basically what I want to do is the following. If my table looks like: risk | reference -----|------ a | 1 a | 2 a | 3 I want a query that will give me: risk | references -----|------------ a | 1, 2, 3
Joining Multiple Fields To A Single Table?
I have 1 table with 2 columns, 'id' and 'name': tbl_names: idname ------ 1Bob 2Jeff 3Fred 4Joe 5Bill I then have another table which contains several fields which hold id's from the above table: tbl_output: idperson1person2person3 ----------------------- 1231 2543 I need a query that will return the names for the specified id from tbl_output. If I have just one 'person' field in tbl_output I would do it with an inner join like this: SELECT name from tbl_names INNER JOIN tbl_names on tbl_names.id = tbl_output.person WHERE tbl_output.id = ? but I can't figure it out when theres multiple fields to be joined from the same table...e.g I want to specify tbl_output.id = 1, and it give me: person1person2person3 --------------------- JeffFredBob
Multiple Tables Of Data, Single Category Table
I've searched and can't find what I'm after, so apologies if this has been covered before. I'm working on a small and simple CMS for a site I'm doing, and just as I was going to start the database I realised something... (I'm using PHP and MySQL) When it's finished, there will be articles, weblogs and content/features. Previously I've done a seperate categories table for each table I have, for example articles and articles_cats. Then a field in the articles table for the category. Now I'd like to use the same categories table for everything on the site. However, I'd really like to have a link table, so each article can have multiple categories. Would I have a table to link articles and cats, then a table to link weblog posts and cats?
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?
Cost Of Single Primary Key VS Multiple Primary Key
Could someone advise me on difference between the cost of using a single primary key and multiple primary key? Especially on their effect on joins? For example, with the following table: ---+-----+-------+----- ID | Name| Class | Test ---+-----+-------+----- is it more cost effective to use a single unique primary key (ID) or use a multiple primary key pair (Name + Class + Test)
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:
|
TRACKER
