Using ORDER BY With UPDATE To Resolve The Duplicate Key
I found this page, which is relevent to my problem:
http://www.mysql.com/news-and-events/newsletter/2003-09/a0000000224.html
I followed the suggestion and altered my query to this:
UPDATE library_categories SET lft = lft + 2 WHERE lft > 1 ORDER BY lft DESC
and I get this error:
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 ' 1 + 1, + 2)' at line 1
Is there a way to get this query to work?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Update If Duplicate
does anyone know how to do an update on a table only if there's duplicate entry in this table using a query?
ON DUPLICATE KEY UPDATE
i have query to set a login time, or update the time if a user is already on the database. $query = mysql_query("INSERT INTO logged (username,time) VALUES ('$userid','$time') ON DUPLICATE KEY UPDATE time='$time'") or die(mysql_error()); and the erorr i get back is You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE time='1110251002'' at line 1 im running MySQL 4.0.23-standard.
On Duplicate Update
I have tried to use the INSERT...ON DUPLICATE KEY UPDATE in Coldfusion. After I run the query the database seems to have the correct entries in it but Im getting an error message: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver][mysqld-4.1.11-nt]Duplicate entry '0-thisURL' for key 2 <cfqueryparam value="#now()#" cfsqltype="cf_sql_date">) v> Am I way off the mark here? Like I say the entries in the database seem sorrect but I still get the error.
DUPLICATE KEY UPDATE
Like many tables, mine has a PK with AUTO_INCREMENT set. I would like to roll my UPDATE and INSERT sprocs together into one to keep things clean. I'm having a hard time getting my head around how this 'ON DUPLICATE KEY UPDATE' works without checking for a null PK or -1 or something like that. In other words, if I'm inserting new data, I don't have the PK value and my table has it's PK column as AUTO_INCREMENT, so how do I handle that? Pass it NULL for the PK on INSERT and a valid PK on UPDATES? In the past I have done this with an IF ELSE block to check for existence, but the MySql docs lead me to believe this is built in somehow.
On Duplicate Key Update
The table has an autoincrement field that is the primary field. It also has id, date, and course_number that are indexed fields. Since the ON DUPLICATE KEY UPDATE how will I Insert and on duplicate update? The ON DUPLICATE KEY UPDATE works only when the field is the primary field. Will it work if the field(s) are indexed?
Update If Duplicate Found
Im currently running a statement that inserts a new row into the table if a duplicate row is not found: PHP $db->Execute("INSERT INTO tee_time ( SELECT $locationid AS locationid, (SELECT courseid FROM course_conversion WHERE coursename = '$currcourse' LIMIT 1) AS courseid, '$currcourse' AS coursename, '$currtime' AS teetime, '$currdate' AS teedate, '$currcost' AS cost, NOW() AS grabdate FROM tee_time WHERE coursename = '$currcourse' AND teetime = '$currtime' AND teedate = '$currdate' HAVING COUNT(*) = 0)"); The problem is if a row is found I need it to update that row instead of just skipping it. On duplicate wouldnt work because teetime and teedate can be the same in multiple rows.
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.
ON DUPLICATE KEY UPDATE Syntax
I have two tables with identical columns. I want to update records in tbl1 with record information from tbl2.Code: INSERT INTO tbl1 SELECT * FROM tbl2 ON DUPLICATE KEY UPDATE SET tbl1.col=tbl2.col, tbl1.col2=tbl2.col2 I seem to be missing something here...
ON DUPLICATE KEY UPDATE And Query Cache
After bludgeoning my head against the table for a bit, I figures out what the problem was that had been plaguing me. When using a INSERT INTO ... ON DUPLICATE KEY UPDATE ... statemtent, it seemed like the update was not taking place. After viewing the webpage in question, (and doing the manual select statement), the old data was being returned. I couldn't figure out why, until FINALLY, I added 'RESET QUERY CACHE' manually after the statement. Then the correct updated info was returned. Apparently the query cache is not flushed after using the above ON DUPLICATE KEY UPDATE statement, like it does with other insert or updates. What do I do now. Just not use that statement? Id like to avoid locking tables if possible. And I cant have the website showing the outdated info.
URGENT: Please Help Me With My ON DUPLICATE KEY UPDATE Query
Could you please help me do this quickly. I have a query: Quote: mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=a") What I need it to do is that whne title and brand matches, it is then a duplicate listing. I have set up the unique key for this. When a duplicate listing is found it will then change the groupname value from "1" and update it with "a". When it then finds the next duplicate listing that has the same title and brand as the duplicate listing that had it's groupname changes to "a", I need it to be change to "b" this time. So I now have two listings that have a groupname of "a" and "b". Now when there is more duplicate listings that have the same title and brand as "a" and "b", I now need the groupname to be updated to the actual listings id and not use "a", "b", "c", etc... How can I do that. I know how I can do the first one so that it updates to 8, but then how would I do the 2nd and 3rd ones. Please help, I have to do this urgent as my database is really slow and it is damaging my site as it is taking about a minute or so to do each query. Also, I would prefer it if it could be done in one single query. I can also use variables in the query and if statements plus other php like Quote: mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=$groupname")
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.
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:
Resolve 3 IDs With Name From Same Table
Scenario... A music track can have featured artists who are listed in a ARTIST table. With the DB structure below... I want to be able to show the names of the main artists and the feature... I can easily do a join on artistid for the main artistid but then i can't join on fartistid#.... Its late and my mind is blank on how to do this... If you even have some PHP loop suggestions... i can try that cuz i am using MYSQL and PHP... but the query solution would be better.... I am assuming there is some nesting going on somewhere.... note: table2.fartistid# = table1.artistid +++++++++++++++++++++++++++++++++++ table1: ARTIST <artistid, name> table2: TRACK <avid, artistid, fartistid1, fartistid2, title>
LOAD DATA INFILE Or ON DUPLICATE KEY UPDATE
I want to use LOAD DATA INFILE to load a text file into my database because of the speed at which it can complete the import. But I don't think I can use it so I want to check here first before I completely nix the idea of using it. Is it possible, using LOAD DATA INFILE and possibly the REPLACE argument, to retain values from specific columns in the row that it's updating. It says in the manual that "You cannot refer to values from the current row and use them in the new row" but goes on to say something about using SET that I don't understand. Is there another way to use LOAD DATA INFILE and to retain values from specific columns that aren't being updated by a field in the text file? (i hope some of that makes a little sense?) If it's impossible to use LOAD DATA INFILE, how can I speed up my ON DUPLICATE KEY UPDATE query. Right now, it just processes 1 row of the pipe delimited text file at a time:
Update Values On Inserting Duplicate Index
I am very new to mysql. I have a question about using the "on duplicate update" clause with insert command. my table "data" has two columns, field1 and field2, where field1 is the index and is "unique". when I run insert ignore into `data` ( `field1`,`field2`) values (1,2) (2,6) (2,9) (5,1) I got 1,2 2,6 5,1 because the third value pair, (2,9) was ignored due to duplication in field1. here is what I want to do: when inserting new record with duplicate index, I want the existing record gets updated. in the above case, I want the output look like the following after the insert command: 1,2 2,9 5,1 after searching mysql online document, I only found the "on duplicate update" clause can do something similar, however, all the examples only show setting the duplicate record to somthing unrelated to the new values.
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 INT UNIQUE Column - Duplicate Entry Error
As an example, I'm creating a table which holds following values in MySql 5.0: idcol lft (unique) 1 1 2 2 3 3 4 4 -> when I try to update the lft column using : UPDATE unique_example set lft = lft +1 where lft > 1; I get following error message : Duplicate entry '3' for key 2 Exactly the same approach works using SQL server 2000. Apparently, MySQL processes the rows one by one, as it throws an error on the UNIQUE lft column from whom I wish to update all rows at once? Code:
Unauthenticated User And --skip-name-resolve
When I show mysql processes I usually find several that look like the following: | 3436942 | unauthenticated user | 192.168.0.52:49607 | | Connect At peak hours, when the website is very busy, this processes multiply like rabits and the service comes close to stop. I searched thru this forum and found a topic which claims this is a bug in mysql and that it can be solved using the --skip-name-resolve option. My question, then, is, if I set this option, do I have to change all the privileges to use IP addresses instead of URL's? What else do I have to do to make sure the database can be accessed?
Can't Resolve This Error, My_thread_global_end() Issue
I am trying to run a very basic php script... It just connects to the server and database displays a message, and closes. The problem is that everytime I do this I get this error: Error in my_thread_global_end(): 2 threads didn't exit
Unable To Resolve Startup Failure
I installed MySql with defaults last night and server worked fine. Made couple of php files and reviewed my handy work in internet explorer no problem. Got home tonight began work again and got error message when trying to access local host. read documentation about startup problems and found error log but do not understand it. A copy follows: ;InnoDB: End of page dump 051108 20:24:39 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Page number (if stored to page already) 0, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0 051108 20:24:39 [ERROR] C:Program FilesMySQLMySQL Server 5.0inmysqld-nt: Got signal 11. Aborting! 051108 20:24:39 [ERROR] Aborting 051108 20:24:39 [Note] C:Program FilesMySQLMySQL Server 5.0inmysqld-nt: Shutdown complete Went to Xp admin services and tried to start couple of times but got same message each time.
Why Can't I Use ORDER BY With UPDATE?
In the UPDATE syntax at www.mysql.com/doc/en it says UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2, ...] [WHERE where_definition] [ORDER BY ...] [LIMIT #] The SQL-query I want to run is: UPDATE `resultat` SET raknasisnitt = 1 WHERE tillhor = 8 ORDER BY `totalt` ASC LIMIT 20 But it won't work... mysql says You have an error in your SQL syntax near 'ORDER BY `totalt` ASC LIMIT 20' at line 1
Update Order By
I cannot add an ORDER BY clause to an update. even though the manual says I can. UPDATE tblBookings SET fldOwner = 'test', fldStatus = 'In Progress', fldRequestDate = NOW() WHERE fldOwner = '' AND fldStatus = 'New' and fldBookingDate > '2006-08-01' ORDER BY fldBookingID LIMIT 1 #1064 - You have an error in your SQL syntax near 'ORDER BY fldBookingID LIMIT 1' at line 4 Version is 3.23.58
SELECT And UPDATE In One Order.
i would like to select some random lines from a table and count that they were selected. My method: SELECT * FROM table ORDER BY RAND(Now()) LIMIT 50; while ($row = entry) do UPDATE SET Used=Used+1 WHERE Id=$rowd[Id] I dont think this is realy fast so i dont like it, is there a faster method?
ORDER BY WHERE + Include Rest Of Table Or ORDER? Possible?
I need to create some SQL that when run returns all the rows which a column is equal to number that has been specified, but then the query needs to return the rest of the rows in the table. How could this be done? is it even possible? Is their order syntax that could do this better?
Multiple ORDER & SUB ORDER On The Same MySQL Table
I am trying to do multiple ORDERS or SUB ORDERS on the same MySQL table, and I'm loosing my mind trying to fathom the logic and SQL statement to use, I'm no MySQL genius! more a newbie. *** SEE ATTACHED IMAGE PLEASE I have tried all sorts of SQL statements, e.g: SELECT * FROM categories GROUP BY parent ORDER BY order, parent DESC Nothing seems to work. I think my only solution is to do a bubble-sort after putting the whole table in a PHP ARRAY ? I'd also like the menu to act like the + pop-open sub-menu boxes on the Forum left column menu.
Selection Order Without Order By!?
I have a table with this structure trans ------- t_no (int auto_increment) t_timestamp (datetime) t_price (double) t_vol (int) I've inserted two record into trans table with same t_timestamp value. When i do a select * from trans it doesn't always return ascending order based on t_no? But sometimes it does? I would like to know how MySql order selection result wihtout the order by command based on this scenario.
Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of precedence in using it. Facts: hcounty is a county that someone puts in a home county in which they live. In the comments they are also allowed to put text, that might contain a county listing also. I want to have the results put the records that have hcounty matches first, then the records that have a county match in the comments field after the first set of records. I have tried all sorts of order by and group by and can't get the result I'm looking for....
Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of precedence in using it. Facts: hcounty is a county that someone puts in a home county in which they live. In the comments they are also allowed to put text, that might contain a county listing also. I want to have the results put the records that have hcounty matches first, then the records that have a county match in the comments field after the first set of records. I have tried all sorts of order by and group by and can't get the result I'm looking for. Any hint or assistance would be greatly appreciated. $query = "SELECT * FROM contacts WHERE hcounty ='$searchcounty' or comments like '%$searchcounty%' order by state, hcounty, dtestart ";
Force Order By Order??
I have 6 rows of data... each has a name, eg: c1, c2, mrgs1, totalfte. they are all under the one column "field". Is there a way I can force their order?? at the mo, I use "ORDER BY field DESC" and its coming out lile mrgs1, totalfte, c1, c2 I need totalfte, mrgs1, c1, c2 is it possible to sort like this??
Custom Order For ORDER BY
I'm grabbing six specific records, like so: $result = mysql_query("SELECT * FROM casestudies WHERE id=1 OR id=2 OR id=3 OR id=4 OR id=5 OR id=6 ORDER BY ___________",$db); I'd like to post them in a custom order, specifically: 1,3,4,5,6,2. Can't figure out how to do this with ORDER BY, if that's even possible. Anything I'm missing?
ORDER BY Out Of Order - Fixed
I have a query that uses two tables that I want to produce output like the following: +-------------+-------------+-----------+ | LOCATION | GROSS SALES | NET SALES | +-------------+-------------+-----------+ | Location 1 | 11,860,735 | 2,907,552 | | Location 4 | 4,814,029 | 1,077,003 | | Location 3 | 2,711,795 | 710,804 | | Location 5 | 2,660,040 | 666,255 | | Location 2 | 2,049,470 | 563,830 | | Location 8 | 2,227,730 | 543,220 | | Location 7 | 1,766,880 | 425,483 | | Location 6 | 1,721,681 | 367,252 | | Location 10 | 13,424 | 2,253 | +-------------+-------------+-----------+ However, I cannot get the "order by" part of the statement to work. I have tried adding a zero to the order by alias. And when I attempt to specify the field in the order by with the calculation and not the alias I get an error. The following examples are queries without the zero and with a zero added to the alias in the order by line. I have also added the details for the table below for reference. Code:
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.
Duplicate Key
I'm trying to create a new table that will store a name in various languages. When I run the following, I get a "Duplicate entry '1' for key 1 " error. I understand that it is because the language ID is the same for the first two inserts, but I don't see how to get around that. Can someone please tell me if the table is setup incorrectly or explain what I need to do to get this to work? DROP TABLE IF EXISTS header; CREATE TABLE header ( page_name varchar(64) NULL, language_id int DEFAULT '1' NOT NULL, PRIMARY KEY (language_id) ) TYPE=MyISAM; INSERT INTO `header` ( `page_name` , `language_id` ) VALUES ('index', '1'); INSERT INTO `header` ( `page_name` , `language_id` ) VALUES ('product', '1'); INSERT INTO `header` ( `page_name` , `language_id` ) VALUES ('product', '2');
ON DUPLICATE KEY
i have finally got on query working, but it does with "40 rows affected" !! There are only 17 records in my totals table, and in this case only one record gets updated. So two queries would have: "INSERT IGNORE..." - only 17 to compare. "UPDATE WHERE..." - only 1 record updated. INSERT INTO v8totals (tid,tyy,tmm,prices) SELECT owners,2007,05,SUM(finalprice) FROM v8x200705 GROUP BY owners ON DUPLICATE KEY UPDATE prices=(SELECT SUM(finalprice) FROM v8x200705 WHERE tid=owners GROUP BY owners); So either i have written my query badly (and i tried many ways) or this ON DUP UPDATE is very inefficient.
ON DUPLICATE KEY
following common query: INSERT ... ON DUPLICATE KEY UPDATE ... Is there more to the UPDATE query than just "UPDATE set a = 'b', x = 'y;" ? I am trying to get the UPDATE query to perform a REGEXP comparison on a field in the table before updating it...something like this: INSERT ... ON DUPLICATE KEY IF (x REGEXP 'y' = 1) THEN UPDATE .... ELSE UPDATE .... END Is this sort of thing impossible?
Not Really A Duplicate
I have a table with 3 fields Directory varchar(10) Server varchar(10) File_Name varchar(10) UNIQUE I set file_name as a primary key because I thought it would be a unique field to ignore all duplicates. below is an example of my data Directory Server File_Name abcd svra testfile abcd svra testfile abcd svra testfile efgh svrb testfile My data is loaded into the database by a comma seperated file. I perfrom the following command load data infile 'svrfiles' ignore into table svr_stuff FIELDS TERMINATED BY ','; it loads the first entry, ignores the rest. I need it to load the 4th entry. removing the ignore statement gives me a dup error. if I did not ignore duplicates there would be a 1000 rows of data that I do not want to see.
Duplicate Row
I know this might sound nuts, but I want to have a way that the user could duplicate a row. Like an entry they put in, and they want to duplicate it. Does MySQL have something that will just duplicate the entry? Such as if I say taw_id = 5 it will duplicate?
Duplicate Key
I am using mySQL 4.1.6 . I have a table A with 3 columns A_1, A_2, A_3. A_1: auto increase field. A_2: unique name A_3: description. Large amount of data will be inserted into this table. And sometimes, it said: Duplicate Key and from that time, I can not insert data until I repair table. I guess there are some problems with the auto-increase field. Have anybody got experience about this? Could you please share the solution
Duplicate Key Name 'x'
when i upgrade a CMS following error occured .PHP Code: Invalid SQL: ALTER TABLE usergroupleader ADD index ugl (userid, usergroupid); MySQL Error : Duplicate key name 'ugl' Error Number : 1061 Date : Script : install/upgrade_302.php?step=1 Referrer : install/upgrade_302.php IP Address : 127.0.0.1 Username : Classname : vB_Database
Duplicate Entry For Key 1
This has me completely stumped, and I've never see anything like it. Here's my query: SELECT mus_searchsynonyms.good_term , mus_searchsynonyms.alt_term FROM s01_MUS_SearchSynonyms AS mus_searchsynonyms WHERE mus_searchsynonyms.active = 1 AND mus_searchsynonyms.alt_term = ".quote_smart($search_query)." AND mus_searchsynonyms.auto_replace = 0 When I use PHP to execute the query, I get the following error message (mysql_error()): (obviously <<$search_query>> is whatever the value of the variable is) Quote: Duplicate entry ?-<<$search_query>>' for key 1 When I run the query in phpmyadmin, the query is fine... First, I don't know why I'd get a key error on a select query, 2nd, there aren't any duplicates in my table...
Duplicate Key Problem
I am quite new to innodb. My problem is a bit funny. I have a table which stores a serial and the status with Id as the primary key which I set. My problem is like this ...
Duplicate Combination
I have a table of the following structure: +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | authorx | int(7) | YES | MUL | NULL | | | authory | int(7) | YES | | NULL | | | sim_num | double(22,4) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ I am getting duplicate combinations of the author fields. For example, I have 10001 10002 2.234 10002 10001 2.234 How can I eliminate the duplicate combination?
Duplicate Results
I have a DB with a name column. Sometimes, the names might duplicated, so i was wondering how to write a query that says, show me 1 of the records instead of both...?
|
TRACKER
