The Most Efficient Manner To Update Multiple Rows
I have a table that holds information about people
The table is quite large (300,000 rows)
I need to write code that update many of the rows in the table
Here is what I need to do
Two of the fields that I store about each person are: location and country
The location is some free text which the user can type in, while the country is the ISO country code, like "US", "ES" and so on
I need go over all the records in the database, and for those that have a non-empty location field, to extract the value and to try to guess what country the user is from and to update the Country field accordingly
My problem is that I might find that most of the records need updating, and this might lead to 100,00+ update statements
I cannot use LOAD DATA as this is product database
Is there a way to update multiple rows (each with a different value and condition in a single query?
View Complete Forum Thread with Replies
Related Forum Messages:
Update Multiple Rows ..
i wnat to update multiple rows at the same time.. i have tried this. but i know is loking for ID=1=2 that will never find... but if i use OR it will update only the 1 one.. PHP Code: UPDATE `externas` SET `impresiones` = impresiones + 1 WHERE `ID` =1 AND `ID` =2;Â
View Replies !
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.
View Replies !
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,
View Replies !
Update Multiple Rows Using Arrays
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:
View Replies !
Update Multiple Rows Within One Table
I have a Dynamically created formfield. Tgis field shows the attached devices off a selected device (for instance you have a pc and in the formfields it shows two attached monitors). The monitors however are in the same database table. (with an other device_id ofcourse). The page with the pc info is shown by using the device_id in the URL. What I want to do is update some fields in this table for the pc but also for the attached devices.... When I do it like this: UPDATE dev SET .... etc. WHERE device_id IN ('#FORM.koppel_2#') It selects both device id's like this: WHERE dev_volgnr IN ('602,603') This doesn't work! However in this example the table should be updated for the devices with device_id 602 and 603. Is this possible? How would I do that?
View Replies !
How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work) $sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";
View Replies !
Update Multiple Rows Using One Query
I have a product table, when one product is out of stock, I want to set the stock status to "0". I'm using the following form to do it. PHP Code: <?php if(isset($_POST['update'])) { $code = $_POST['code']; $stock = $_POST['stock']; $sql= "UPDATE product SET stock=$stock WHERE itemcode=$code";Â Â Â Â $query = mysql_query($sql,$conn); if ($query) {echo "<H1>UPDATED</H1>";} else {echo "<H1>Update Failed</H1>";} } else { ?> <div> <form method="POST" action="<?php echo $PHP_SELF ?>"> Â Â Â Item Code: <input type="text" name="code"><br> Â Â Â <br> Â Â Â Stock Status: Â Â Â Â <select name="stock"> Â Â Â Â <option>Please Select</option> Â Â Â Â <option value="0">Out of Stock</option> Â Â Â Â <option value="1">In Stock</option> Â Â Â Â </select>Â Â Â Â Â Â Â <br> Â Â Â <br> Â Â Â <input type="submit" value="Update" name="update"> </form> </div> <? } ?>
View Replies !
Can't Update Multiple Rows Via PHP, But Works In PhpMyAdmin
My php script will update 1 row fine, but fails on 2. However, the exact same query works when pasted into phpMyAdmin. THIS QUERY WORKS BOTH IN MY PHP AND PHPMYADMIN: $sql = "update table set color='red' where id='1';"; $sql_do = mysql_query($sql); THIS QUERY WORKS ONLY IN PHPMYADMIN: $sql = "update table set color='red' where id='1'; update table set color='green' where id='2';"; $sql_do = mysql_query($sql); I know the second query is correct, because when I copy it from my php code and paste it into phpMyAdmin, it works. But only the first query, with 1 row update, works from my php code.
View Replies !
UPDATE Multiple Rows In Mysql (in One Single Query)
trying to UPDATE multiple rows with mysql. I know how to do it with multiple queries but i think it would be less resource consuming generating mysql query code with php and update all one single step. here is the method i usually employ: $value_column_1 = array(); $value_column_2 = array(); .....
View Replies !
Efficient UPDATE
I have a table with the following structure; CREATE TABLE my_table ( id_1 int(11) NOT NULL , id_2 int(10) NOT NULL , stauts tinyint(1) NOT NULL DEFAULT 0 , PRIMARY KEY (id_1) ) Engine =InnoDB'; The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query; UPDATE IGNORE my_table SET id_1 = 74240, id_2 = 5 I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.
View Replies !
Efficient Way To Count Rows
I'm trying to get the number of rows in a table with a very large number of records in it (~9 million). When I run a select count(*) for some criteria (where name='something', etc) it takes around 6-8 secs for the query to return the value. I tried by using SQL_CALC_FOUND_ROWS with a very small LIMIT but then the query was taking even longer. I'm using InnoDB, with query caching enabled. I could look at the information_schema and get the approximate row count but whenever I use a where clause it'll be way off mark.
View Replies !
Efficient Ways To Retrieve Specific Rows.
My site used forum software that I wrote myself (in Perl) which, up until now, has used flat files. This worked fine, however lately I've been wanting to do more stuff with user accounts, and had been eying MySQL for over a year. Finally I've decided to start off small by converting the forum's account system to a MySQL database (and convert the rest later after I'm comfortable). So far everything is working fine, and I've figured out how to create the table, insert records, modify records, and so on. However I had a question on what was the most efficient way to retrieve information about a user as I read through the flat file containing messages. As each message is read I want to find that user's relevant information, build it into my output, then continue on to the next message. Now here's my problem. Since I'm reading a flat file in a way that was once trying to be somewhat memory efficient (on really slow server way back when, trying to avoid arrays and hashes) I'm finding that I'm having to send separate questions to MySQL as each message comes up. Say from 1 to 20 very simple questions to complete printing the page. Also, sometimes the questions might be repetitive - since I'm not storing any of the results in memory, if a user appears twice I ask about it twice. Would I really be better off trying to find a way to consolidate all distinct users into a single question? Or is MySQL efficient enough that this isn't really much of a concern?
View Replies !
Efficient SELECT From Multiple Tables With Same Formatting?
Let's say I have 2 tables with the exact same formatting (field/column names, etc) -- the only difference is the name of the tables.. one is called "table1" the other is called "table2" If I wanted to query both of the together is this legal and good coding practice? "SELECT id, headline, permalink, body FROM table2007_11, table2007_12 WHERE id=23" understandly I get this error: Column 'id' in field list is ambiguous" is there a better way to do this than printing out each table name for each field individually? (ie. id. table2007_11, id. table2007_12, etc)?
View Replies !
Most Efficient Way Of Storing Data From Multiple Accounts
As part of a system I am putting together I need to allow users to create thier own accounts on my servers. Each user can create their own account, and then have their users register for it. Each account needs it data seperate from the others, a member registered for one account should not be able to view another account and a username registered with one account should still be available to the other accounts. The ways I have been looking at are: 1. Create a new database for every account created so that all users are kept in seperate databases. 2. Have one table for users, one for topics, one for posts etc and then associate each row within this table with the relevent account. So for example a user could register with the forum with the ID 4, so their user entry would be Userid: 234 Username: xxxx Password: xxxx Forumid: 4 Then when a new member registers with any account I simply check that there is not another user with the same account ID and username. Indexes on relevent fields in this system could help speed up huge tables. I expect to quickly have 20,000 plus accounts (and in theory it could go up to hundreds of thousands). I guess my question is which of these methods is better from a speed point of view once we get a large number of accounts and users. Also, are there restrictions on the number of fields in a table that could cause problems? Using MySQL by the way, on an Apache server.
View Replies !
Query To Retrieve 5 Records Immediately Before And After A Particular Record In Sorted Manner
Just consider a simple table with one integer column (however numbers are not inserted in sorted manner and some of them may be missing). Given a particular record, say 32, I would like to retrieve 5 records immediately before and after this record in ascending manner of sort. So, in this case I should get 25, 27, 28, 30, 31, 32, 33, 34, 37, 38, 39 (I did not have 26, 29 and 35 in the table) Why do I go about writing such query?
View Replies !
Select Multiple Rows With Multiple Values In A Single Statement
when selecting multiple rows with different values in a certain row.. is this proper syntax? SELECT * WHERE name IN ('hello', 'cookie', 'smile', 'police', 'fun') It seems to work fine, but It came from another SQL version I believe .. not mySQL. Just wanted to double check, or see if there is a more correct way.
View Replies !
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).
View Replies !
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 .
View Replies !
Multiple Update By Multiple Users
I'm looking at producing an application that will allow multiple users to order multiple items/ parts from what would effectively be an online store. These users could be ordering several hundred items/ parts at a time, each item/ part ordered representing one box or several hundred. Each part record will require the "total stock" to be down dated and each unit will require updated to show it as having been picked (plus other info). I'm looking at programming this in php 4.3 or php 5 using a current version of mysql. Looking around etc. I believe there are various options options open to me : 1 Create a pick list in a database table and have a "pick" routine pick the stock. This would ensure that the actual pick could only be carried out by a single process and the result of the pick request could be emailed to the user. This approach would provide the user with a very fast, responsive acceptance of an order request (but not real time - albeit almost). 2 Use Table Locks - This I understand may be the fastest but potentially could leave users with apparently hung screens, eg. if three picks were issued each taking 10 secs, then pick 3 would not start until the first two picks completed (20 secs). 3 Record Locks - I'm concerned that doing this would increase the time taken to pick the stock. But if the times were not excessive this would provide the user with an immediate "real time" response.
View Replies !
Update All Rows In A Column
I'm a complete newbie but I'm trying to do something that I would think would be easy. In my table I have a column LICLEVEL with values of Club, Expert and Elite. I need to tack on "USA Cycling - " in front of that value, so that if the current data is "Elite" after the update it will say "USA Cycling - Elite". With 800 records to update, I'm hoping there's a fairly easy way to achieve this with SQL (or PHP).
View Replies !
Update One Field In All Rows
I have a mysql database which contains 24,000 rows. I added a field called "RECALL", which contain null values. What I'm trying to do is fill that field in all 24,000 rows with the value "N".
View Replies !
Update All Rows Conditionally
Having a bit of trouble getting this syntax right. It needs to go through each and every row, read a field and if that field = a certain text, update another field in that same row. if(locate('text to locate' , `dbName`.`tblName`.`fld1`) , update `dbName`.`tblName` set `dbName`.`tblName`.`fld2` = 'new text') .
View Replies !
Rows Changed In Update SQL
Is there a way to find out how many rows got updated when i execute a update stmt. I would like to get the updated rows count in SQL. Let me know and Thanks in advance. update stmt where clause; now how can i get the count of rows that got updated.
View Replies !
MySql UPDATE With Duplicate Rows
I'm trying to run an update on a linking table, the update is running into a Primary Key constraint violation, and in my workaround I've got stuck trying to write a DELETE statement. Here's the table I'm working on: Code:
View Replies !
Update Statement :: Affected Rows: 0
UPDATE `zipLocations` SET `z_zipCode` = '06001' WHERE `z_zipCode` =6001 LIMIT 1 ; Field Type Attributes Null Default Extra Action z_zipCode int(5) No 0 z_cityName varchar(50) No s_stateAbbr char(2) No z_countyName varchar(50) No z_areaCodes varchar(50) No I even went through phpmyadmin found the row clicked on the edit button for that row and updated it. and it still doesn't update.. im' thinking it has to do with the data type being INT... Why doesn't it update it ?.. It says it was successful. Affected rows: 0 (Query took 0.0002 sec)
View Replies !
Import A Csv To Update Only Certain Rows Of Data?
I have an existing mysql table and a csv. I want to import the csv into the table, but it is at a different sort order. There are too many records to sort my csv by hand to match. There is a common 'cd_code' field that both have. Is it possible to import a csv to the table so that each row of the csv is inserted in the correct row of the table, based on the common 'cd_code' field? For example, if I sorted by cd_code asc, it would match the csv, but how to I import the csv into a particular sort?
View Replies !
Update Script, But Check If The Value Is The Same On Other Rows
I have a nice user script. In a particular section there is the ability to edit the users on the databse. What I am concerned about is that people may edit the username or email and find its the same username or email that another user has, this can screw up the login system especially two usernames being the same. So is it possible to make it so that if the email/username is the same in another row to give an error, Please note that the input fields for their email and username are already filled in so if they dont come to edit it them they will still output there original username/address.
View Replies !
MySQL Update Speed With 10K+ Of Rows
I Have table called `users` which have 10K+ rows. Each user have a fields called `gold` and `level`. Now each user should get "pow(1.25, level)*100" gold per/hour and it should divide so that users will get 1 gold per time unit. example: user level is 7 and he should get 1.25^7 * 100 gold = 476 gold per hour (1 gold every 7.5 seconds). Now I have a problem. I can't update all users at once every second because it would create too much load for server (mysql load was 100% if I did it). Even if I would do this update in every 7 seconds, it will be too much load for the server. maybe we can determine user gold amount so: user gold = user gold + (seconds_after_last_update/3600); and do update every prime hour? but what happens the when user wants to spend this gold? My mysql-updateing daemon is running in infinite loop doing 1 action every second.
View Replies !
SSH: Select Database And Update Rows
I'm trying to update a few rows in my database via SSH. I have multiple databases on my server and I need to know what I'm doing wrong. I login via SSH and enter $: mysql -u root -p I want to select the database and then run my update command, but when I type. > select [db_name] It just jumps to a new line without any prompt. Can anyone tell me really quick when they do to select a database?
View Replies !
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?
View Replies !
Multiple-table UPDATE.
I'm trying to update multiple-tables at the same time but I read somewhere that you could do it if you had mysql version 4.0.4. But currently, I have version 4.0.0 and I want to know how to do it with this version
View Replies !
Multiple UPDATE Entry
I just want to update 2 or more things at the same time... but dunno how... here's what i would use:Code: UPDATE users SET location = 'changed' , password = 'new pass' WHERE username = 'david' OR SET location = 'changed too' , password = 'new pass too' WHERE username = 'brett' But that doesn't work, (it's not like i thought it would anyway)... how would i update 2 or more different fields at the same time?
View Replies !
Multiple Update In 1 Query
I am trying to do the following using PHP and Mysql: $sql=" UPDATE records set appear_order='2' WHERE id='19'; UPDATE records set appear_order='3' WHERE id='17'; UPDATE records set appear_order='4' WHERE id='18'; UPDATE records set appear_order='5' WHERE id='20'; UPDATE records set appear_order='6' WHERE id='16'; "; I am getting the following error message: " Cannot update records: 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 'UPDATE records set appear_order='2' WHERE id='19'; UPDATE recor" I don't know why this isn't working. when I paste this query to phpMyAdmin (I hope you guys know phpMyAdmin) - it works. somehow using a PHP code it does'nt - I get that error message. did anyone every come across this error and knows how to overcome it?
View Replies !
Update Multiple Columns
I get an error on this command: UPDATE patchMapTable SET row=0,column=0,patchNumber=23 "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 'column=0,patchNumber=23' at line 1" row, column, and patchNumber are columns in patchMapTable all 3 are int row and column are primary keys
View Replies !
Update Multiple Fields With 'OR'
Is there a better way to do this (more efficient). I have up to 200 items to update per query, the id's are not sequential. Using up 200 (well 199) OR's in a single query doesnt strike me as being the best way to do this. All the fields get set to the same value. # Example UPDATE table SET value=value WHERE id=11 or id=23 or id=31 or id=74 or id=56 or id=96; # Up to 200 fields Or am I stuck with this query? (which works).
View Replies !
Update With Multiple Instructions
I have about 1000 updates required on a table. The syntax i know is for example Update t1 set b_id=9264 where b_id=4259; and then the next one would be Update t1 set b_id=9358 where b_id=2253; etc etc Is there any way i can run an update with multiple instructions?
View Replies !
Multiple Update Same Table
I have a table that i need to have only 1 field in the same column be yes and the rest turned to no... I have tried SELECT * FROM tab1; UPDATE tab1 SET 'field' = YES WHERE id = 5; UPDATE tab1 set 'field' = NO WHERE id != 5; Obviously running two at the same time does not work but if i can somehow combine these two to do it all at once...
View Replies !
Update Multiple Fields
I need to update some 900,000 records, each different. I will be pasting the code into the sql window of PHPMyAdmin at my web site. The individual updates look like this: UPDATE table SET Field1='sample', Field2='sample1' WHERE UniqueID=12345 UPDATE table SET Field1='sample2', Field2='sample3', Field3='yes' WHERE UniqueID=2021432 How can I string together a bunch of these where the SET field values are always different and there is one each for 900,000 different ID's. Right now, I have these in 30 text files but I can't seem to get the syntax correct.
View Replies !
Multiple-table Update With Sum()
I have the quantities from different warehouses for each product in products_quantities, and the aggregate quantity for each product in products. I want to update products with the total from products_quantities. Why does this not work? UPDATE products p, products_quantities pq SET p.products_quantity = SUM(pq.quantity) GROUP BY pq.products_id I've tried every variation I can think of, but always get a syntax error near 'GROUP BY pq.products_id'. Can I not update one table with the sum of values from another table, in one query? I'm using 4.0.26.
View Replies !
Update Multiple Tables?
situation: a user wants to change their username on my site and their username is littered accross many tables in the DB. If in every one of those tables i've got a static "user_id" column as well as the "username" column, what is the best way to update all of the different tables with the most efficiency?
View Replies !
Best Way To Update Multiple Tables At Once
I need to update a few tables at once. I have a form, were I fill in some information about a concert, which updates thez concert-table. Next to this, I need to update the bands-table, the statistics-table, and some others. What would be the safest, and fastest way to achieve this?
View Replies !
Update Numerous Rows In A Single Query
Let's say I have 6 rows in my MySQL table ID RANK ============= 1 1 2 2 3 3 4 4 5 5 6 6 I want to delete ID #3, that's ok, BUT I want to downgrade all the rows that has a RANK value higher than him (3). So my table would look like this afterwards; ID RANK ============= 1 1 2 2 4 3 5 4 6 5 I want to be able to update a field within the query, something like this $deleted_rank=3; //the rank number that got deleted mysql_query = UPDATE tablename SET rank='rank--' WHERE rank>'$deleted_rank' the field RANK is an INT I know the rank-- thing doesn't work, but I want to know how I can achieve this in a single query, I don't want to do a WHILE on this.
View Replies !
How To Update/Delete Bunch Of Rows Quickly?
I have a textarea that allow user to edit a bunch of rows of data quickly. It either add/delete/update rows. How do I do it quickly? The db table has rows of names. e.g. the db table has: car |Susan dog |John monkey |Chris John enters: plane boat house After processing, the db table should say car |Susan plane |John boat |John house |John monkey |Chris How do I update the db table quickly? If I use update query, it will update John's rows but there is no rows to update? There are only rows to add and delete at this time. I don't want to delete all records of John every time John enters the data, because a user can potentially have a lot of rows in the table.
View Replies !
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?
View Replies !
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'?
View Replies !
|
TRACKER
