UPDATE Or INSERT
I am looking for a solution to an update-type problem. Users enter name, address, etc etc. I have set the first_name & last_name fields to be jointly unique, so that no two records can exist with the same first and last names the same (e.g, no 2 records with 'Joe Bloggs' as first/last names.).
Now if users want to correct, say, their address, it will not allow them to since a record already exists with their first/last name.
Is there a SQL statement that will allow me to bypass the UNIQUEness constraints when other fields need to be updated?
(And related, if the UNIQUE fields are updated, it currently creates a new record but leaves the old one there. I need to overwrite the old record but keep the same ID number primary key.)
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
UPDATE Or INSERT
Kind of worked myself into a weird place in my current application. Where I find myself at is with a single form that is supposed to handle both updates of existing rows and insertions of new rows. The way I want this to work is: 1) If the ID is specified and references a valid row, update that row 2) If the ID is specified but does not reference an existing row, insert a new row 3) If the ID is not specified, insert a new row Now, (3) is easily done in my application - just test if the ID was specified by the user. Simple. Nothing to it. What I'm wondering is if there is a way to do both (1) and (2) in a single MySQL query and receive feedback such that I can in my application inform the user whether an update or an insert took place. I could do this with two seperate queries (query for the ID; if I find the row update it, otherwise insert a new one), but I'd like to do it with a single one if possible (mostly because I want to expand my skills with complex SQL queries).
Update Or Insert Sql
I am using this query to update tableone based upon the values in tabletwo. I really like this query because it works very fast. PHP UPDATE tableone n, tabletwo c SET n.sec = c.sec, n.cat = c.cat, n.published = 1 WHERE n.section = c.section AND n.category = c.category This works great however I cant seem to get it to insert or append if the data already exists. It just overwrites the data. My difficulty is that I need certain columns to be updated (overwriting the data) and other columns to be inserted or appeneded to (adding to the data). Is a stored procedure the best way of doing this? Or, is there a way of adapting my sql to cater for this?
Need To Use UPDATE Instead Of INSERT INTO
I need to UPDATE exisiting rows and populate the fields using only the resources in this INSERT statement. INSERT INTO cms.project_event (projectPhaseID,sortOrder) SELECT cms.projectTemplate_eventTemplate.projectTemplatePhaseID, cms.projectTemplate_eventTemplate.sortOrder FROM cms.projectTemplate_eventTemplate WHERE cms.projectTemplate_eventTemplate.projectTemplateID = '1' Any clue pointing in the right direction will be of great help. Im not providing alot to go on just the to update the two fields from the other table instead of INSERTING new rows.
Last Update/insert
Can I know the time when the last UPDATE or INSERT occur in a table in a database?
UPDATE And INSERT In One Go?
Is it possible to create a query that will do an UPDATE or an INSERT depending on certain data already in the database? Eg I'd like to record a user's vote. If this user hasn't voted already, an INSERT is called for, otherwise an UPDATE. Right now, I'm SELECTing first, checking whether the user's id is already in the votes table, and if yes, my query's an UPDATE, otherwise it's an INSERT. This needs two hits on the DB however, and I'm someone who likes to limit the amount of DB hits as much as possible.
Update And/or Insert?
I'm using MySQL with PHP. I want to update a record if it already exists. If it does not exist, I need to create a new record. Is this done through a simple SQL command, or do I need to use PHP to negotiate two separate UPDATE and INSERT statements?
After Insert, After Update
I am taking my first and last class in programming as I can't make sense of the textbook or teachers instruction (but I am determined to get at least an A or B in this class.) So if anyone could tell me in VERY simple terms how to write an after insert and after update trigger I would be very much grateful.
INSERT / UPDATE
I'm having trouble using the IF statement. What I want is to do either a INSERT or a UPDATE depending on the SELECT statement. No mather what i do, it gives me an error on the INSERT statement. Isn't allowed to have a INSERT/UPDATE statment inside a IF statement? This is what i'm trying: SET @param = (SELECT) IF @param IS NULL THEN INSERT... ELSE UPDATE... END IF
INSERT Or UPDATE
I need using a SELECT statement, but is there a way to make this work on INSERT or UPDATE statements as well? In other words, I can do: SELECT MONTH(deadline) FROM projects ... But can I do something like: UPDATE projects SET MONTH(deadline)='11' ... where the rest of the date stays the same, but only the month is updated? Doesn't seem to work, and I don't see any examples in the manual comments, but it sure would be nifty.
Update Or Insert
I have to create a database with 8.000 entries. It will be the top entries it will have. Now... what is faster ? a) to create the table before with 8.000 rows and the id field from 1 to 8.000 (primary key...) and then do UPDATE's b) to create the rows as they have to be created with INSERT's ? In the middle of the process it will be SELECT's from other users.
Insert If I Can't Update
I check for the data to exist. If it does, I UPDATE, otherwise I INSERT. Is there a way to do it better, more efficiently?
INSERT/UPDATE
I've got an array in PHP, which is identical to a row in mysql: Code: array ( 'key' => 'somekey', 'column1' => '...', 'column2' => '...', 'column3' => '...', ); If 'somekey' does not exist in the table, I want to INSERT this array as a new row. Otherwise, I want to UPDATE the row which has the key 'somekey'. Is it possible to do this in a single query? Cause now, I'm first INSERTing a row and if that fails, I try an UPDATE instead.
Insert/update
I have a table of x/y values and a set of 'new' x/y values. For each of my new x values that already has a record in the table, I want to add the new y value to the corresponding existing y value. If there is no x value in the table, I want to create a new record with the new x/y values.Is there a simple (atomic) way of doing this?
Update / Insert / Delete Log
I'm wondering if there is any way to get a lot of recent update/insert/delete statements performed on a db by a particular user?
Insert Then Update That Record
I'm using PHP to insert contact information in to a database. Confidentiality requirements have made the design change so that the private information be entered separately. I've modified Form 1 so that it contains the public information and added a button. The button does two things, it inserts the record in to the database then draws the private information form. My question is, can mysql give me the last row inserted so I can pass that information on to the form and use it in my update query? I tried doing it by ID in that table but the id is not assigned until after the submit button is pressed. I can do another query but thought it would be slick to do it by row.
Insert On Duplicate Key Update
Is it possible so do something like this "insert * from TempTabel on dublicate key update 'all fields'; " So I don't have to write every column name. I need this, so I can use the same code on multible tabels. I could use replace, but then the foreign key in other tables are not updated.
If Exists Then Update, Otherwise Insert
provide a very simple if, then, else clause in MySQL? If a customer named 'bob' is already in the database, I want to update his id. If he is not already in the database, I want to insert him. What would be the best way to do this? I get errors for the following: IF EXISTS (SELECT 1 FROM customers WHERE name = 'bob') THEN UPDATE customers SET id = '007' WHERE name = 'bob'; ELSE INSERT INTO customers(id, customer) VALUES('007', 'bob'); END IF
Two Timestamps For Update And Insert
I need to record date updated which a timestamp does, but also the date inserted. Can I do this all from the mysql database, or do I need to have a variable date inserted in the php. The first option appeals to me more.
Simple Insert And Update
Im having a rather irritating problem..because its so simple and I just cant figure it out.In my database I have some company info and the primary key "companyID" is used to reference it in several other tables. however in phpmyadmin I cannot figure out how to set it to automatically generate when I create a new company in the company table and then propogate to all the tables it links too.
Unable To Update And Insert
I have a weird problem which is i able to access to the DB, able to do a select but unable to update and insert. This is the scenario. The existing running db is called as 'abc'. I dump it out and the i restore it as 'abcv2' and add in few column and insert value in. Then my application able to access the db. Able to do a select but unable to perform insert and update. No error is show in my application. Application wise i not the problem as it able to do so with others db. I suspect is the privileges problem therefore i create a user and grant all access to that user. But the same thing as unable to update and insert. Any idea of my problem? I think if i change the database name from a dump file won't cause this problem right. What i can do now is i get the dump file then i open it with notepad and do the necessary changes. This is working fine but i would like to know what is the cause for this problem.
Insert Or Update Records
What's the best way to achieve this? To simplify the problem, lets say I have a table with two columns, 'id' and 'value'. Now, I want to pass an object through remoting (amfphp, irrelevant really but anyway) to either update existing rows or insert new ones. The object I'm passing has corresponding fields, and if there is a match on the 'id' field then update it, if not insert a new row. So I guess I need to first pull out all of the id's and compare them to the ones in my object, then seperate them up to ones that need updating, and ones that need inserting. I can't quite see what the best way of doing this is. Either in code e.g. PHP or is there a MySQL statement to do it the quick way?
Insert And Update Entire Table
My website will modify records on my products table. But I need to occasionally update their qty and their prices without losing any changes already made by other users online (like qty sold). I'm able to do that now. It works really well:
Select, Update, Insert. Efficiently?
First, I want to say that I didn't design the databases and I wish I could fix them, but I can't. There is a table, s01_Baskets, that tracks the users basket throughout my store. There is a table s01_StoreKeys, that holds the next ID for every field used in tables. In this case I'm interested in is the basket_id. What I need to do is create a new "basket" for a customer. To do this I need to select the key for this basket_id: SELECT s01_StoreKeys.maxvalue FROM s01_StoreKeys WHERE s01_StoreKeys.type = 'Baskets' Then I can "create" a basket by inserting a row: INSERT INTO s01_Baskets ( s01_Baskets.session_id , s01_Baskets.cust_id , s01_Baskets.basket_id , s01_Baskets.order_id , s01_Baskets.order_proc , s01_Baskets.last_update , s01_Baskets.ship_id ) VALUES ( '".session_id()."' , Ɔ' , Ɔ' , Ɔ' , Ɔ' , '".microtime()."' , Ɔ' )"; Then I have to update that field for next key for the next basket. UPDATE s01_StoreKeys SET s01_StoreKeys.maxvalue = '".$basket_id+1."' WHERE s01_StoreKeys.type = 'Baskets' UGH. I'm not thrilled doing it like this (one bad query, or if another process jumps in at the wrong time, I've messed up the baskets....) Is there a better query for this? I'm pretty sure I can do the insert and select as one, but I need the basket_id(+1) for the update also. I don't know of any way to do all three queries in one. Can anyone see a better way to do this queries (ignore the poor design)?
Auto-increment And Update/insert
I have the following problem with a MySQL update/insert. my problem lies in the fact that I have to insert a new row between 2 existing rows. the table is sorted by ID and has ID as auto-increment so I cannot insert a new row between 2 rows as inserting will assign the insert a new ID number... I figured now I have to do an update and then loop till the end of the table and then do an insert at the end..
INSERT And UPDATE Similar Statements?
At least as I know it, INSERT goes like: INSERT INTO table (Col1, Col2) VALUES('Hello', 'Goodbye') While UPDATE goes like: UPDATE table SET Col1 = 'Hello', Col2 = 'Goodbye' Is there any way I could make them the same format, such as: UPDATE table (Col1, Col2) VALUES('Hello', 'Goodbye') ?
Insert On Duplicate Key Update Question?
I have UNIQUE comp keys with 3 columns. I want to insert new rows if the data doesn't match one of the 3 columns. If they match, don't do anything. I am trying to do it with one query without doing select first and bunch of comparsion then either insert or update depending on the comparsion. I am think of using this: INSERT INTO mytable ('col1', 'col2', 'col3') VALUES ('mydata1', 'mydata2', 'mydata3') ON DUPLICATE KEY UPDATE col1=col1, col2=col2, col3=col3 is it going to work? I think it first tries to insert my data as a new rows if it is not duplicated. If duplicated, it will update the data with its old data. But update will ignore the operation since it's the same data.
Convert UPDATE Query To INSERT
I have an SQL dump file of a sql database that was generated with the UPDATE export type. The file contains code for generating the various tables inside the database along with the UPDATE lines for the table data. Is there a way to process the dump file such that all of the UPDATE instructions are converted to INSERT instructions? I have to restore the database from scratch so there are no existing table rows to update.
Update / Insert Data Using CSV File
I need to update a field in my database using a CSV file. The database already has the Product Number (pNUM) and I need to add Wholesale Price (pWholesalePrice). The file I have is fomatted as follows: AC1074,48 AC1076,60 AC1076A,60 AC3515,60 AC3521,60 P1207,78 AC2300RH,42 etc... I would need to do some sort of UPDATE that would find the product number and insert the corresponding wholesale price. I can't imagine this would be difficult if I knew what I was doing.
INSERT Or UPDATE Depending On Row Data....
I have a form that submits to a php script which in turn sends the data to a table in a mysql database. $sql = "INSERT INTO scores (scoreid, xuser, xscore) VALUES ('', '$_POST[xuser]', ('$_POST[xscore]'))"; $result = @mysql_query($sql,$connection) or die(mysql_error()); ?> This is the main section of that specific script at the moment. As can be seen, there are three fields, scoreid, xuser and xscore. What i want to do is Insert a new users score as it is currently done so, HOWEVER, if the user already exists in the database then instead of a new tuple being entered for them, how can i do it so that their old tuple is overwriten/updated instead?
Update Record, Insert New If Doesn't Exist
I have a table that contains a list of parts used in a repair. The table contains foreign keys to the part and job concerned. it also contains data of time taken and status. This data is read into a gui and the user can update the time taken and status. The user can also insert new parts in the gui. is it possible to write an sql statement that will update the existing records and insert new ones if none already exist? at the moment I'm having to try and update each record one, at a time and check the number of rows modified, if zero then insert a new record. all of which is done within the application. It works but doesn't seem very elogent...
INSERT / UPDATE A Row By Its Position In The Table?
Is there a way to insert or do an update of a row (aka record) by its position in the table, for example, the 5th row of the table? I know you can select data in such a manner, using the LIMIT 4,1 clause, for example. Does this work with insert/update as well, and if so, what's an example SQL syntax?
No Read Or Write Between INSERT And UPDATE
I am using mysql with the InnoDB engine. I wrote a perl script that first selects something from a table, and then updates a second table based on the select from the first table. I need to make sure that there is no read or write to the tables while my script performs the insert and update. I looked at http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html and it says this: --------------------------------------------------------- If you are using a storage engine in MySQL that does not support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown here requires LOCK TABLES to execute safely: LOCK TABLES trans READ, customer WRITE; SELECT SUM(value) FROM trans WHERE customer_id=some_id; UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id; UNLOCK TABLES; Without LOCK TABLES, it is possible that another thread might insert a new row in the trans table between execution of the SELECT and UPDATE statements. --------------------------------------------------------- However, I am using InnoDB and it DOES support transaction. So, does that mean that even if I don't lock my tables, it will still work? If not, what do I need to do?
Error On Update Or Insert Data But No Message
I have an application on asp and mysql 5.0, connecting with ODBC 3,51. When the application insert data or update data, MySql don't send error message, but the data don't update or insert. The transaction is ignored!!!!. Then, on my application I can't detect error. My code: set Command1 = Server.CreateObject("ADODB.Command") Command1.ActiveConnection = MM_base_STRING Command1.CommandText = "UPDATE EXAM SET MaAnt='"&MaAnt&"',Mois='"&Mois&"',Annee='"&Annee&"',[...]WHERE Id_Exam='"&id&"'" Command1.CommandType = 1 Command1.CommandTimeout = 0 Command1.Prepared = true Command1.Execute() Have anybody an idea?
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?
Truncate Tabel And Insert Data How To Update Indexes?
Once every day our financesystem updates data that I uses for my database. Some tables I truncate and reinsert data to get the latest changes in our customer and prospect database. Others I delete from a certain date to obtain best economical data and finally one database with 14M records I add records that has been added since the day before. What I find is a very slow system and the funny part (but I think understandable) is that if I do not update for lets say a week (because I test stuff) the queries are fast and smooth. That tells me that the problem is indexing. So if I truncate a table, how to update indexes or can I do that at all? The Optimize tabel doesn't help me here :( I use MyISam and not InnoDB would that make any changes? What about indexes are they needed (those I create)? or can MySQL handle that better?
MySql Update Or Insert Statement To Modify A Word In All Caps
I have a field in my database called Subtopic. In this field I have a word LITERATURE in all caps. I need to change the word LITERATURE to LITERATURE followed by a colon then an empty space. Can someone give me the MySql statement I can paste into phpMyAdmin to make this change? Note: The Subtopic field contains the word "literature" but not in all caps because the Subtopic field is part the database that is an encyclopedia. Therefore, I want to change ONLY the word LITERATURE if the word LITERATURE is in all caps.
Query (select/insert/update)Slows When Table Grows
I have a mysql database where tables are very simple. An example table will look like this: create table myTable( token varchar(255) not null primary key, token_count int default 1, frequency int ) type=INNODB; A java program populates this table. The program reads from files (in batch mode) and inserts into myTable with following logic: (1) See if the String to be inserted is present in myTable.(Uses SELECT) (2) If not present the insert into myTable (uses INSERT) (3) If yes update the table after adding the current frequency (USES UPDATE) After processing each file issue COMMIT. This goes on well for some time - but after the Table grows over 100,000 rows the process slows down considerably. The Strings I 'm handling is very large and I'm not sure how well Mysql behaves with very large String as primary Key. My select and update statements are all based upon primary key(token in this case) - so question of setting index doesn't arise. Even then I have set index on token field - but it has degraded the performance. Also I have set the query_cache_size to 1MB even then it is not enhancing the performance. A sample file has almost 30,00 to 50,000 lines. Each line having a string + blank + frequency. After processing first few files - the Java program takes large time to process a single file - and subsequently the processing time increases exponentially.(as the tabel grows in size it slows down).
INSERT/UPDATE W/data Conversion To UTF8 (ex: Josè Feliciano)
I have the following string of "Josè Feliciano", when I insert/update into the mysql db I need the db to convert the [è] to the UTF-8 value of [è]. Is this possible via the db... I'm sure it is... if so, how? I have also tried the following and do not see the expected result. CONVERT(_latin1"Josè Feliciano" USING utf8) Also, when I do use CONVERT it chokes when there is a single-quote in the value that I am inserting... even with the value is surrounded by double-quotes. What is up with that. CONVERT(_latin1"Ramblin'" USING utf8) ?? CONVERT(_latin1'Ramblin'' USING utf8) ??
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?
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.
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; }
|
TRACKER
