Multiple UPDATE
i'm trying to update 3 different rows in my DB...can this be done with one query ? i tried a couple different methods, but kept giving me errors
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Multiple Update.
I am starting with a datbase structure like this: ID | filename | info1fromfile | info2fromfile ------------------------------------------------------- 1 file1 2 file2 My query loops through the filename column and then opens each file listed in turn. Whilst open it extracts strings from the text files. These are to be entered into the rows relative to that filename. So I should end up with this: ID | filename | info1fromfile | info2fromfile ------------------------------------------------------- 1 file1 someinfo1 someinfo2 2 file2 someinfo3 someinfo4 To be able to run the UPDATE I need to know which row I am currently on. Is there a way to do that as the filename QUERY pulls out the filenames? I guess Ideally I need to know what ID we are on. Also is there an easy way to count how many filenames have been looked at so I can use LIMIT so that the UPDATE statement doesn't try to UPDATE the whole database every time. (The number of files will get larger over time so I only want to update the rows necessary!)
Multiple Update
I have two simple tables t1 and t2. I want to update the price field in t2 with the values of the (selected) price field in t1. I have followed other threads regarding similar problems and have worked out that these two attempts should work. Alas, they don't. Anybody any ideas? UPDATE t2, t1 SET t2.price = t1.price WHERE t1.item_code=t2.item_code AND (((t1.selected)= 1)) UPDATE t2 INNER JOIN t1 ON t1.item_code=t2.item_code SET t2.price = t1.price WHERE (((t1.selected)= 1)) I am pasting these directly into mysql using phpmyadmin.
Multiple Update
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.
Multiple UPDATE
I have a txt file with around 5000 lines that look like UPDATE `members_user` SET `user_forums` = '0' WHERE `user_name` = 'user' when i go to SQL query in phpmyadmin and give it 1 of the lines individually it will take it, and when I give it 2 lines, or all of them it wont, saying there is a syntax error. I used 'find/replace' to format the file so each line is exactly the same, so obviously it wont allow me to do this command more then 1 at a time, but I cant do every single line like that, there must be a faster way.
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?
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,
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?
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.
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.
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:
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).
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.
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?
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
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
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?
UPDATE Multiple Tables Difficulties
I've been creating a stock update system that needs to be integrated with an existing database. To update the stock I need 2 tables updated in the same query (unless there is better way). In one table, items_ordered, holds every single product that has been sold and has a field StockUpdated which holds a 1 or 0 depending on whether they have been stock counted already. This will stop future queries counting stock that has already been counted. i.e. the query will look and update each record that has a 0. So far the query is able to update this table successfully.....
How To Update All Fields With A Multiple Of The Field Value?
Is there a MySQL command to replace all the values of a field with the field's original value that's had a mathematical formula applied to it? Basically, I want to divide the field's original value in half, then add 40% more. The formula would be y=x/2+.2x where y is the new value and x is the original value. I know I could write a PHP script that would select each field's value, apply the formula, then update the new value, but it'd be cool if there's a MySQL built-in function to do this.
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'";
Update Syntax For Multiple Columns
I'm looking for a feature I'm accustomed to with other SQL products, whereby multiple columns can be updated with a single SET clause, e.g.: UPDATE tab1 SET (col1, col2, col3) = (SELECT col4, col5, col6 FROM tab2 WHERE tab2.col1 = tab1.col1); I don't see this feature documented for MySQL. Is there a way to accomplish it?
Multiple-table UPDATE Does Not Work
I installed mysql-server-3.23.54a-11 and mysql-3.23.54a-11 rpms on RH9. Everything works fine. But when I try multiple-table UPDATE statement, it show me syntax error: SQL statement: mysqk> update country,city set country.cCode='A' where country.cCode = '1' and city.areaCode = country.cCode; Error message: ERROR 1064: You have an error in your SQL syntax near 'city set country.cCode='A' where country.cCode = '1' and city.areaCode =country.' at line 1
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?
Update Function With Multiple Databases
We are looking for a way without having the possibility to install the last version of MySQL to do the equivalent of Update t1,t2 set t1.fielda=1 where t1.key=t2.key and t2.field='processed' meaning that we want to be able to update certains values in the table t1 depending on values coming from t2 having the same key number.
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.
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?
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?
Incremental Backups Using Update Logs With Multiple Databases
I've set up my server to keep update logs for incremental backups, as described in Kevin Yank's book (Build Your Own... 3rd Edition), but I've noticed that the update.* files have entries from all the databases on my server. Is there a way to automatically split up the log files so that I can have separate update.* files for each database?
Running Multiple Update Querries In Query Browser
I am trying to run multiple update statements together from the MySQL Query Browser. For example: update table1 set field1 = "123", field2 = '456' where rowid = 'abc' update table1 set field1 = "321", field2 = '654' where rowid = 'efg' I can run them one at a time, but I cannot run multiple updates at the same time. I have 1100 of them to run, so I don't want run them individually. In M$ SQL I can just put a ; or GO at the end of each line to seperate it from the next. How can I do this in MySQL?
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.
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).
Connect/ Join Query Multiple Dbs Multiple Servers
Struggling to connect simultaneously to remotehost@my_ip_address AND localhost@my_field_db FROM my_field_tablet_pc to affect a custom sycnchronization that SQLyog will not allow. Both remotehost and localhost are built on WAMP stacks.
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 .
Complex Query - UPDATE Within UPDATE?
Edit: Before anyone leaves this thread, don't be put off by the regular expressions! They are not the problem, so please stay and read. OK, this query has got my head spinning. I am basically creating a query that goes through each product in a table to update the stock for that particular item with that particular size (i.e. I am talking about shoes - different models and each model has different sizes (uk kids 12 -> uk 11). With each shoe it does (or is meant to do) the following: 1. The PHP script that runs the query is looping through every size outside of the query 2. So for each of these sizes it checks to see whether the product it is currently on matches the size it is on 3. When it finds the size it is on, it then deducts the correct number of units from the stock table 4. The final WHERE clause makes sure this subquery inside the UPDATE only happens when the StockUpdated field of the Product table equals 0 (in other words, the stock hasn't been counted before) Basically what I need to do, is first to make sure what I currently have got does the above correctly but also I need the query to UPDATE the StockUpdated field to 1 only when it has been updated successfully. How could I do this? Unfortunately I cannot just add an extra update entry to the end of the query as this would update the StockUpdated field regardless of whether it has been properly counted or not. Here is the query I have so far (with a little simple PHP around it doing the loop): PHP $shoesizes = array(1 => 'ukk12','ukk13','uk1','uk2','uk3','uk4','uk5','uk6','uk7','uk8','uk9','uk10','uk11'); $numshoesizes = count($shoesizes); for($i = 1; $i < $numshoesizes; $i++) { $stockupdate = " UPDATE heelys_stock,items_ordered SET heelys_stock.size_".$shoesizes[$i]." = (SELECT CASE WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]12( )?(' -- if UK Kids 12 THEN heelys_stock.size_ukk12 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]13( )?(' -- if UK Kids 13 THEN heelys_stock.size_ukk13 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]1( )?(' -- if UK 1 THEN heelys_stock.size_uk1 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]2( )?(' -- if UK 2 THEN heelys_stock.size_uk2 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]3( )?(' -- if UK 3 THEN heelys_stock.size_uk3 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]4( )?(' -- if UK 4 THEN heelys_stock.size_uk4 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]5( )?(' -- if UK 5 THEN heelys_stock.size_uk5 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]6( )?(' -- if UK 6 THEN heelys_stock.size_uk6 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]7( )?(' -- if UK 7 THEN heelys_stock.size_uk7 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]8( )?(' -- if UK 8 THEN heelys_stock.size_uk8 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]9( )?(' -- if UK 9 THEN heelys_stock.size_uk9 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]10( )?(' -- if UK 10 THEN heelys_stock.size_uk10 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]11( )?(' -- if UK 11 THEN heelys_stock.size_uk11 - (items_ordered.Amount/items_ordered.Price) FROM items_ordered WHERE items_ordered.StockUpdated = 0) WHERE (heelys_stock.id = (SELECT heelys_stock.id FROM heelys_stock,heelys_shoe WHERE SUBSTRING_INDEX(items_ordered.Product,',',1) = heelys_shoe.full_shoe_name)) , items_ordered.StockUpdated = 1" // at the moment this last update of the items_ordered table happens to every record!!! even if the other part of query fails // update stock for size $i mysql_query($stockupdate); } Hope someone can see how I can do this? I've been working on this query for 2 or 3 hours now and I've been making reasonable progress but now I am really stumped.
Update Replication, Force Update
Recently an error in the db on my master caused the slave to fail. I noticed this after a few days when I looked at the status and it said "Slave_SQL_Running: No". After looking further I saw what the error was on the master. What is the best method of re-synching the databases? Is there a command to force a re-replication or synch of the dbs? Would you delete the slave's db and update over? In this case, is there a command to pull the data down from the master?
Unable To Update - Have Update Priveledge
I've been granded update priveledge to tables in a few databases on our server: Select_priv ,Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, and Lock_tables_priv are all set to 'Y' for a selected database. I can connnect and read the data and data definitions just fine. I cannot insert, update, delete, create, or alter any table in this database. Our admin worked for a couple of hours yesterday trying to resolve the issue, but no joy. I've googled for this problem and the most relavent posts I've found were problems related with users not being able to connect. I can connect and I can read, I just can't do any of the other priviledges.
Multiple Languages, Multiple Products
I posted before and was helped immensely. I am designing a multilingual-capable site where each product may (or may not!) have a description in the user's chosen language. In this case, I want to get the default language description. Say my product descriptions table has fields prod_id, language, and description. When I have a single product and I want its description, I can retrieve it as follows:
Multiple Batabases Vs. Multiple Tables
I am about migrate from an old program database to MySql (Running under RH LINUX)and I'm wondering which is the best option to do : I currently have one file for each of my modules, (I'm using filepro plus) , so like CUSTOMER, WARRANTY, ZIPCODES, INV, etc... like 2,000 files each with their own fields. 1) Should I create multiple databases on MySQL 2) Should I create a single database on MySql and with multiple tables ? Which one of these options is the best and more safer ?
Multiple Databases Or Multiple Tables
I have an application that is supposed to make multiple connections [around 10 connections per second] to a mysql database. The connections are done by mutlitple users at potentially the same time. I have to decide if I should use : 1)one database with one table for all users. 2)one database for each user. 3)one database with one table for each user. I would really appreciate if you can tell me what choice is best and why.
ON DUPLICATE KEY UPDATE - Don't Have A Key But Want To Update
This may seem like more of a PHP question but I will come onto the MySQL part in a minute. Basically I have a PHP script that lets users upload 2 different CSV files to different tables. The problem I have is that sometimes these CSV files are updated and then need to be reimported into the database through the script. I have made this script totally generic so that when you change the table that is being entered to the rest of the script will alter itself to import the correct CSV to that table. But a problem arises when I have a CSV file that contains each product sold and the shopper who bought the product (so the shopperID may appear in more than 1 row) - however this file does not have a unique id for each row meaning that when I use an INSERT INTO with a ON DUPLICATE KEY UPDATE... it will just insert more rows instead of updating rows because there is now key in the table. The format of the table is like this (with some example data to show you more clearly): +-----------+-------------+---------+------+--------+------+ | ShopperID | Product Code | Product | Price | Amount | Units | +-----------+-------------+---------+------+--------+------+ 546733 1315 prod 1 64.00 64.00 0 546733 1316 prod 2 43.99 43.99 0 OK just ignore the last 2 fields - that is a problem with the old system there was which does not add the units up right. Anyway basically I might have the same shopper ordering 2 things which will come up on different rows - I have 3 things I cannot do: 1. I can't make the ShopperID field the PRIMARY KEY because I have many times where there are 2 rows with the same shopper 2. And I cannot just do an UPDATE when it gets to a duplicate entry because it will overwrite every time it gets to a shopper that already exists. 3. I don't want to add a unique primary key to the CSV as this will mean that the system I am creating loses its ability of being automatic because the person (an admin) that uploads the file will have to make sure that it has that extra field which is auto incrementing. Does anyone have any ideas on how I can get round this? Here is my PHP function which contains the query: PHP // this function for insert data to csv function makeINSERTS($text, $table, $tablefields, $correctcsv){ $insert = array(); //make array for hold data insert $i = 0; $success = true; while(list($key, $val) = each($text)){ // Insert the data $insert[$i] = "INSERT INTO ".$table." VALUES('"; $insert[$i] .= implode("','", $val); $insert[$i] .= "') ON DUPLICATE KEY UPDATE "; foreach($tablefields as $k=>$field){ $insert[$i] .= "`$field`='{$val[$k]}',"; } $insert[$i] = substr($insert[$i],0,strlen($insert[$i])-1); // echo $insert[$i].'<p></p>' $result = mysql_query($insert[$i]); if(!$result) { echo 'FAILURE to insert/update any or all of the database form uploaded CSV!<br />' $success = false; die('Query failed: ' . mysql_error().'<br />'); } $i++; } if($success == true && $correctcsv == true){ echo 'SUCCESS in uploading the CSV file to the database!<br />' } else { } return $insert; }
Update Queries Do Not Always Update
We are running a MySQL database using PHP to create html pages and forms to update and add information to the db. Sometimes the update query works, sometimes it doesn't - or at least, not permanently. A new record may be added, but five minutes later it has sometimes, but not always, mysteriously disappeared. Where should I look for a solution? Is there a cache that MySQL creates that may be being accessed unwittingly? We are using the standard php and MySQL versions that come as standard on a Mac OS X Tiger 10.4.7 server. The browser we are using is Firefox 2.0.4.
Need To Update PK Of Every Row
I want to update every row's primary key and need an SQL statement to do it. There are 2 columns PK Placename So I firstly want to order all the rows by placename in alphabetical order and then starting by updating the first row to 1, I then want to update every ID with an increment of one - all the way until the final row is updated.
Log Update In My.ini
If i put log-update=update in my ini it works fine,but as soon as try to specify a different location i.e. log-update=/backups/update i cannot access mysql. I have a folder called backups in the data folder and one in the MySQL Server 5.0 folder (just incase), but it still will not work.
Update If?
I have a table that has an item field, a price field, a price min field, a price max field. I want to be able to store the current price for an item as well as it's high and low. Is there a way to do an update on the row for the new price as well as compare the new price to the min field and max field and then update them if the new price is either higher or lower than the current values in the min and max field? I know how to do it with multiple queries and comparing, but am looking for a clean way in one query.
|
TRACKER
