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")
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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.
How Can I Take Comma(,) As String In Mysql Query (was: Urgent)
how can i take comma(,) as string in mysql query.. actually i have a field value like (asd,sdf,asr,asf), (sdf,sdh,aqb), ...... now if someone searches for 'aq' then (sdf,sdh,aqb) value should be shown.. i think u guys got my point ...
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...
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.
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?
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:
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:
Hi, Need Urgent Help On Sql Select Statement
I have two tables that stores product details which is carinfo and products.I did it in 2 separate tables as the products are by diffrent category and therefore the fields are different. I'm not sure if that was the correct way doing it coz i seem to have problem now. The problem is,each product purchased are stored in a order table and that table only stores the product ID. The items in the first two table which is carinfo and products, have both unique ID like p1 and c1. My problem now is when i'm trying to get the product information of item in order table i need to check if that is from product table or carinfo. Coz i wanted to generate a report that will list the item in order table together with the items information. The sql i use is "select p.productid,p.prodname,p.proddescription,c.productid,c.prodname,c.proddescription from carinfo c,product p where p.productid='p14' or c.productID='p14' group by p.productid,p.prodname,p.proddescription,c.productid,c.prodname,c.proddescription" You can view th example results here. http://www.ineers.com/meg/error.JPG the results i get are the product information of the item if its in product table,but the quesry also results to list all the product name in carinfo table. I hope my question is clear. Its urgent and i've been breaking my head thinking how to solve this without having to change my database design which is to combine both carinfo and product table as one.
Urgent Select Statement
I have two tables Table 1 named Alan1 alan1_id | alan1_name | alan1_gtip 1 | bruce | 12 2 | brad | 13 Table 2 named Alan2 alan2_id | alan2_name | alan2_gtip 1 | lee | [12] 2 | pitt | [13] I have been trying to join these tables using like or other commands to obtaion the rows like this. But i could not get over the [ ] in the second table by sql. 1 bruce lee 12 2 brad pit 13
URGENT SECURITY ISSUE
I have a .asp application in which certain parts, clients can now edit/update/add information to their records in our DB. I basically use an include statement at the top of each page. The include .asp page has my connection data within .asp tags. The general 'user' to which the connection belongs only has 'select' facilities within the db. If I now want to let a client update/delete/add information, then surely giving them access to Update/Delete & Add is very high risk ?. If they manage to gain access to our Username & password, then they could delete all our information ?. What is the standard workaround for this scenario ?
Urgent - Load Sql Commands From Txt File
mysql -u root -p -D mydatabase < 'c:/sqlcommands.txt' All I ever get is the 'filename or volume' invalid, or something like that. It's not an issue with the path to the file, because I use c:/filename for other kinds of imports and it goes in fine. I urgently need to load some sql commands from this file else I have a lot of work to do.
Urgent Syntax Error In Importing .sql Files
I am trying to import .sql file which has create table and Insert table statements. I cannot use phpadmin as size of the file is too large. test is the databse and guestdb is the file whihc needs to be imported. I am giving command at sql prompt mysql> -u [username] -p test < c:/ ....guestdb.sql Error : there is error in sql server.
Query Help To Remove Duplicate Data
I have a query that returns: first name, last name, comments. I would like the result to be something like: Dan | Hudson | "here is a comment" | | "here is another comment" Lisa | Hudson | "my comment" but currently the name is repeated every time there is a comment. Is there a way to do a group by clause or something that will remove the duplicates? Dan
Query That Duplicate Row And Increment Date
I have a table EVENT id date duration 1 2006-01-01 3 2 2006-04-23 5 3 2006-10-01 0 is possible made a query that do it? id date 1 2006-01-01 1 2006-01-02 1 2006-01-03 1 2006-01-04 2 2006-04-23 2 2006-04-24 2 2006-04-25 2 2006-04-26 2 2006-04-27 2 2006-04-28 3 2006-10-01
Eliminating Duplicate Results From Query
I'm trying to dynamically build pull down menus based on the contents of a column (in order to build a search query). But the column may have the same data entered multiple times. This results in a pull down that has the same option listed several times. I wonder if I can eliminate duplicates from that array? So that it only appears once in the array.
Duplicate Yet Diferent Data In Sql Query
Is there a single SQL Query that will do the following? mysql> SELECT * FROM user; +----+----------+ | id | username | +----+----------+ | 1 | Bill | | 2 | Bob | | 3 | Hank | | 4 | Jim | +----+----------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM email; +----+-------+------+---------------------------------------+ | id | _from | _to | message | +----+-------+------+---------------------------------------+ | 1 | 1 | 2 | Blah, blah, blah | | 2 | 1 | 3 | I told Bob to Blah, Blah, Blah | | 3 | 3 | 4 | Bill told Bob to Blah, Blah, Blah | | 4 | 4 | 2 | Did Bill tell you to Blah, Blah, Blah | +----+-------+------+---------------------------------------+ 4 rows in set (0.00 sec) What SQL Query would result in the following...??? mysql> SELECT ??????????????????????????????; +----+-------+------+---------------------------------------+ | id | _from | _to | message | +----+-------+------+---------------------------------------+ | 1 | Bill | Bob | Blah, blah, blah | | 2 | Bill | Hank | I told Bob to Blah, Blah, Blah | | 3 | Hank | Jim | Bill told Bob to Blah, Blah, Blah | | 4 | Jim | Bob | Did Bill tell you to Blah, Blah, Blah | +----+-------+------+---------------------------------------+ 4 rows in set (0.00 sec)
Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is: The key of one of the record has the format: Rssss The key of the second record has the format: ssss How can I delete both records from the table?
Ignore Duplicate Query Items
This formcode retrieves faxes from a DB but some are the same and I do not want those in my list... how do I tell it to ignore duplicates?PHP Code: $sql = "select c.fax as fax ". "from #__comprofiler as c ". "left join #__users as u on c.user_id = u.id ". " where (c.user_id = u.id) and (u.block =0) and (c.fax != '') and (u.usertype != 'Super Administrator') and (u.name != 'guest') ". " order by c.fax";
Help With Updating Duplicate Rows In Mysql Query.
Quote: SELECT id, count(*) AS numlist FROM products GROUP BY category, name, brands HAVING numlist > 1 ORDER BY id ASC What it does is find the duplicates. I also have a column called "app" which has a default set to "1". So now what I want to do is that when duplicates are found, I want the first duplicate row in each group to stay as a "1" and the others in the same group to be updated to a "2". I need this done for every group. How can I do this. I have looked high and low accrossed the web, but can't seem to find any solution. I have managed to find out how to group them and count the number of listings in each group, but I can't seem to figure out how to do the rest. Also, if possible, I would like it to be done with one query.
URGENT: Error 1316 On Install Of MySQL Connector/ODBC Driver 3.51
I have my Vice President's laptop and trying to install the ODBC drivers for reporting that he needs.. and am facing a time crunch. I'm getting the following error, which I've never run into before. I thought the setup file was corrupt, so I re-downloaded her from another mirror, but I get the same error. ERROR 1316. A network error occurred while attempting to read from the file C:DOCUME~1cbockLOCALS~1Tempmysql-connect or -odbc-3.51.12-win32.msi
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 Query
I have a query question. I want to update a database that contains ips like 90.30.100.xxx. I only want to modify the third numer (100). Does anybody know how can i do this? ex: i want to modify all ips from 90.30.100.xxx to 90.30.101.xxx. Notice if i had 90.30.100.100 i'd like to become 90.30.101.100
Update Query
I'm selling tickets and I need to be able to reduce the total number of tickets when one is sold. I have a table with the total number of tickets stored in a field and and a field with the quantity sold in a particular purchase. I think this is a fairly simple update query can anybody help get me started?
Update IN Query
Code: UPDATE SET COUNT=COUNT+1 IN WHERE SELECT sponsor,mstatus, count( username ) from customer Group by sponsor DESC Having count( username ) = 2
Update Query
I am trying to do a custom sortup/sortdown function in php on a mySQL basis. Now I got that prob: I got a table, that has a "sort"-field for each data like that: data | sort a | 1 b | 2 c | 3 d | 4 e | 5 The output of the data is ORDER BY sort ASC. Now I want the users allow to change the "ranking"/"sorting" themselves that way, that they click e.g. on sort entry d one up. So the new table should look like that: a | 1 b | 2 c | 4 d | 3 e | 5 Does anyone know how to do that in one or only a few mySQL queries? I mean I could read out the whole table and then do a php-loop giving new sort-numbers to each data and then update every single data, but I guess there should be an easier way in mySQL. My prob is that I suck big time at mySQL
UPDATE Query
Select TransType FROM TransTypeMapping WHERE vTransType=”CANCEL”, into array[0] For each record from MainTable where TransactionCode = TransType.array[0], Find record[1] where record[0].Category= record[1].Category and record[0].Units1 = record[1].Units1 Action: Set fields in MainTable, record[0].vCancel = T; record[0].vMatchingTransID = record[1].vTransID Set record[1].vCancel = T; record[1].vMatchingTransID = record[0].vTransID
Update Query
I am using phpMyAdmin to update table rows where the short_descr field (text) contains no characters. I can execute this: SELECT * FROM download WHERE short_descr=''; But this gives me an error: UPDATE download SET delete=1 WHERE short_descr='';
Update Query
Code: UPDATE users_tickets SET dateResponse=NOW() and Response='sgdgdsgdsfdsfds' WHERE ticketID='1' i query the same database earlier in the code and it works fine. in phpmyadmin i tried editing it and it worked fine. this is the code phpmyadmin returns when you do it there Code: UPDATE `users_tickets` SET `Response` = 'mommy' WHERE `ticketID` =1 LIMIT 1 ;
Update Query
i need a query to change my customers_id number from an example 1000549 to 500600 and i made this query that doesn'work : UPDATE customers SET customers_id = '50%' WHERE customers_id = '100%' i need to change the first 3 digits from 100 to 50 and the other numbers stays the same.
Update Query
Here's my table set up:Code: USERS user_id,firstname,lastname,tel,institution,email,username,password BOOKS book_id, user_id*,created,author,subject,title,description BOOKCHAPTERS chapter_id, book_id * CHAPTERS chapter_id *, chapter_name, chapter_location Im trying to update the chapter_name and chapter_location in the chapters table. The parameters I pass in seem to be ok, so I think it's the SQL update query below. Have I done the update query correctly?Code: update chapters c, bookchapters bc, books b set c.chapter_name = ?, c.chapter_location=? where b.book_id = ? and bc.book_id = b.book_id and c.chapter_id=? and c.chapter_id = bc.chapter_id;
Update Query
Can we update row when there is an empty field? here is an example (attached): How can I update the x? Let's say x = 6 Like: UPDATE table SET [all_empty_fields]='x' WHERE [field_before_that] ='5';
Update Query
I have the following query but I need some help on its structure (for example where do the joins come in): update new_residence, new_respolis, new_district, new_person set new_residence.respolis_id = new_respolis.id where new_person.poll_number = new_respolis.prov_poll_num and new_person.district = new_district.abbreviation and new_respolis.prov_elect_dist_id = new_district.id and new_person.id=1 I tried to run this query but it took forever to execute.
Update Query.
I have 2 tables. BOOKCHAPTERS (book_id,chapter_id) CHAPTERS(chapter_id,chapter_name,chapter_location) I want to be able to update the name (chapter_name) of chapters given a book_id. The chapter_id for each book is simply and auto-incremented column. I currently have the following queery which doesnt seem to work. Code: update chapters c, bookchapters bc set c.chapter_name = ?, c.chapter_location=? where bc.book_id = ? and c.chapter_id=? and bc.chapter_id = c.chapter_id; Now I know you're gonna say 'What's the value of all the prepared values' but I just want to know if the syntax for the query is ok.
Slow Update Query
I have about 2000 update queries to do, which takes about 1 hr on 250,000 rows. My table is getting kinda slow here is the query i am using UPDATE nametable SET sectionname = replace(sectionname,'".$oldsec."','".$sec."'), categoryname = replace(categoryname,'".$oldcat."','".$cat."'), published=Ƈ' where sectionname='".$oldsec."' and categoryname='".$oldcat."' ; I am wondering if the same thing is possible with an insert... on duplicate key statement? I cant seem to get the insert statement to work, but not even sure whether it is appropriate. With this query I am basically finding and replacing some columns based upon another table (within the php script I am using)
Update Query Question
I want to update the col 'type' in one of my tables. I want to change the value from 4 to 5 in that col but the query i use below is not correct. Can anyone point me in the right direction? Quote: UPDATE ��' SET type = Ƌ' WHERE type = Ɗ'
Stuck On Update Query
I am struggling with a SQL statement and hoped someone would be able to help customer_depots contacts Each table contain fields called 'customer_depot_id' and 'address_id'. What I want to do is update the address_id in the contacts table, with the value of the address_id in the customer_depots table, only where the customer_id values match in each table, AND the address_id in the contacts table is equal to zero. Does this make sense? So, update address_id in contacts, where address_id in contacts = address_id in customer_depots and address id in contacts = zero. This is because a number of address_id's are missing from the contacts table, and I want to replace them with the correspondingvalue from the other table. I have figured out a SELECT statement as shown below, but I need an UPDATE statement as explained above. SELECT cd.ADDRESS_ID FROM customer_depots cd, contacts con WHERE con.CUSTOMER_DEPOT_ID = cd.CUSTOMER_DEPOT_ID AND con.ADDRESS_ID = 0
Select And Update In One Query
My remoteurl table has 3 columns: id, hits and url. PHP UPDATE remoteurl SET hits=hits+1 WHERE id=2 SELECT url FROM remoteurl WHERE id=2 Instead of using two queries I want to use one query which could select url and update hits column. Is it possible?
|
TRACKER
