Help Optimizing UPDATE Query
I have 1 query in my script that is taking waaaaay too much time.
Is there a way that I can speed up or optimize this query?
Right now the query is taking about 600+ seconds to update 500 rows.
The Query
PHP
$update_from_sas_postmeta = "UPDATE wp_postmeta, sas_postmetaSET wp_postmeta.meta_value = sas_postmeta.meta_valueWHERE wp_postmeta.post_id = sas_postmeta.post_idAND wp_postmeta.meta_key = sas_postmeta.meta_key";$update_from_sas_postmeta_result = mysql_query($update_from_sas_postmeta) or die("MySQL ERROR (update_from_sas_postmeta_result): ".mysql_error());
The Tables
CREATE TABLE `wp_postmeta` (
`meta_id` bigint(20) NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL default Ɔ',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=294705 ;
CREATE TABLE `sas_postmeta` (
`meta_id` bigint(20) NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL default Ɔ',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Sample Data
2, 2, 'page_template', 'default'
3, 3, 'merchant_id', ?'
4, 3, 'merchant', 'SomethingSomething'
5, 3, 'link', 'http://www.website.com/link.html'
6, 3, 'thumbnail', 'http://www.website.com/thumbnail.jpg'
More Details
When the UPDATE query begins to run, the sas_postmeta table will have 500 records in it and the wp_postmeta table will have about 250,000 records in it.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Optimizing A Query
I want to optimize a very expensive query, the query joins 5 tables, each having tens of thousands of records, always returns about 15000 matches and performs several calculations on each and every records (a datadiff, five time the / operator and two * operators), and to end with, all records (probably about 45000) are grouped and sorted resulting in 15000 records. This takes about 80 seconds, I want to get it down to max. 2 seconds :-D All indeces are made, so I won't be able to gain anything there anymore. Does anybody has a clue as to how I could proceed here (maybe something with caching, materialized views and triggers?).
Optimizing Another Query
I have another Query, that I don't manage to optimize. It's hard. I tried to first readout the ID's and then readout the contents, but this is not faster ... Here is the Query: SELECT o.id AS oid, o.time AS otime, o.disNaviReferer AS showR, o.disNaviToplist AS showT, o.disNaviPartner AS showP, o.description AS odesc, o.type, o.used, o.html, c.id AS cid, c.name AS catname, e.id AS eid, e.beschreibung AS linkdesc, e.flag, e.freigeschaltet AS etime, e.previewPic as prev, e.hits FROM own_content o LEFT JOIN link_entries e ON e.link = o.id LEFT JOIN link_cats c ON c.id = e.cat_id WHERE o.used = 1 AND type = 4 ORDER BY etime DESC LIMIT 0, 30 Here ist the table structure: -- -- Tabellenstruktur für Tabelle `link_cats` -- DROP TABLE IF EXISTS `link_cats`; CREATE TABLE `link_cats` ( `id` tinyint(2) unsigned NOT NULL auto_increment, `name` varchar(100) collate latin1_general_ci NOT NULL default '', `cflag` tinyint(2) NOT NULL default Ƈ', PRIMARY KEY (`id`), KEY `flag` (`cflag`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=13 ; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `link_entries` -- DROP TABLE IF EXISTS `link_entries`; CREATE TABLE `link_entries` ( `id` smallint(11) unsigned NOT NULL auto_increment, `flag` tinyint(2) NOT NULL default '-1', `eingetragen` int(10) unsigned NOT NULL default Ɔ', `freigeschaltet` int(10) unsigned NOT NULL default Ɔ', `cat_id` tinyint(2) unsigned NOT NULL default Ɔ', `beschreibung` varchar(100) collate latin1_general_ci NOT NULL default '', `link` varchar(255) collate latin1_general_ci NOT NULL default '', `extern` tinyint(2) NOT NULL default Ƈ', `previewPic` varchar(255) collate latin1_general_ci NOT NULL default '', `hits` int(11) NOT NULL default Ɔ', `voteAnzahl` smallint(10) unsigned NOT NULL default Ɔ', `VoteSumme` mediumint(10) unsigned NOT NULL default Ɔ', `poster` varchar(100) collate latin1_general_ci NOT NULL default '', `poster_hp` varchar(100) collate latin1_general_ci NOT NULL default '', `ip` varchar(15) collate latin1_general_ci NOT NULL default '', `mail` varchar(100) collate latin1_general_ci NOT NULL default '', PRIMARY KEY (`id`), KEY `cat_id` (`cat_id`), KEY `flag` (`flag`), KEY `link` (`link`), KEY `freigeschaltet` (`freigeschaltet`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6242 ; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `own_content` -- DROP TABLE IF EXISTS `own_content`; CREATE TABLE `own_content` ( `id` smallint(5) unsigned NOT NULL auto_increment, `html` text collate latin1_general_ci NOT NULL, `description` varchar(255) collate latin1_general_ci NOT NULL default '', `time` int(10) unsigned NOT NULL default Ɔ', `ip` varchar(15) collate latin1_general_ci NOT NULL default '', `used` tinyint(3) NOT NULL default Ɔ', `last_used` int(10) unsigned NOT NULL default Ɔ', `used_by_fl_id` smallint(5) unsigned NOT NULL default Ɔ', `type` tinyint(2) NOT NULL default Ƈ', `disNaviReferer` tinyint(2) NOT NULL default Ƈ', `disNaviToplist` tinyint(2) NOT NULL default Ƈ', `disNaviPartner` tinyint(2) NOT NULL default Ƈ', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=299 ; Thank you very much! This query is very important for me to be optimized, because I execute it quite often with diferent data ...
Optimizing A SELECT Query
SELECT id, gameid, name, type FROM media ORDER BY date DESC LIMIT 5 SELECT * FROM games ORDER by wishlist DESC LIMIT 5' SELECT * FROM games ORDER by mygames DESC LIMIT 5 SELECT mediaid, count(*) AS number FROM download WHERE date > ��-08-14 10:44:43' GROUP BY mediaid ORDER by number DESC LIMIT 5 SELECT * FROM news ORDER BY id DESC LIMIT 14' SELECT gameid, count(*) AS number FROM views GROUP BY gameid ORDER by number DESC LIMIT 10 All of these queries I have determined are inefficient (using some tools) and they all have the same thing in common. They're all trying to find the "Top 5" or "Newest 10" or whatever from a table, but in doing so they are going through every single value and comparing them. What other way is there to do this same idea of getting the "top 5" of a certain column values, without the SQL having to check every entry?
I Need Some Help Optimizing This Query Of Death
I'm working on a ruby on rails web application and I have one query in it that just kills the entire site. The basic concept is that I have a medias table and I can tag it (a tags table and a taggings table that holds the relationship). What I want to do is find similar items out of the media table by pulling the medias that have the most similar tags. This is the current query I have: SELECT medias.*, COUNT(acts_as_taggable_tags.name) AS acts_as_taggable_tag_count FROM medias LEFT OUTER JOIN taggings AS acts_as_taggable_taggings ON acts_as_taggable_taggings.taggable_id = medias.id LEFT OUTER JOIN tags AS acts_as_taggable_tags ON acts_as_taggable_tags.id = acts_as_taggable_taggings.tag_id AND acts_as_taggable_tags.name IN ('video','funny','kitten') WHERE (medias.id != 849 AND (medias.status = 1 AND medias.publish_on <= 񟭆-07-25' AND (medias.expire_on > 񟭆-07-25' OR medias.expire_on IS NULL)) AND type != 'PluggedMedia') GROUP BY medias.id, medias.type, medias.user_id, medias.status, medias.publish_on, medias.expire_on, medias.position, medias.title, medias.description, medias.views, medias.comments_count, medias.url, medias.ip_address, medias.total_views, medias.legacy_url, medias.created_at ORDER BY acts_as_taggable_tag_count DESC LIMIT 4; These are the indexes I have on the tables: medias: +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | medias | 0 | PRIMARY | 1 | id | A | 875 | NULL | NULL | | BTREE | NULL | | medias | 1 | medias_publish_on_index | 1 | publish_on | A | 291 | NULL | NULL | YES | BTREE | NULL | | medias | 1 | medias_publish_on_index | 2 | position | A | 875 | NULL | NULL | YES | BTREE | NULL | | medias | 1 | medias_legacy_url_index | 1 | legacy_url | A | 875 | NULL | NULL | YES | BTREE | NULL | +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ +----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | taggings | 0 | PRIMARY | 1 | id | A | 4873 | NULL | NULL | | BTREE | NULL | | taggings.........................
Optimizing A SELECT .. WHERE .. LIMIT Query
(using MySQL 5.0) First, the problem: I need to select different "pages" of IDs that match a small list of categories. Here's my table: CREATE TABLE `testTable` ( `id` int(10) unsigned NOT NULL auto_increment, `category` tinyint(3) unsigned NOT NULL, `username` char(15) NOT NULL, PRIMARY KEY (`id`), KEY `cat` (`category`) ) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1 And an EXPLAIN of my query: EXPLAIN SELECT * FROM myTestable WHERE category IN(5,8,11) LIMIT 5000,20; id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | myTestable | range | cat | cat | 1 | null | 49904 | Using where So from that we can see it's hitting 50,000 rows to get the 20 rows that I want (it's a table with 1,000,000 rows total). I've been using MySQL for a fair amount of time, but I haven't ever done much optimization beyond the standard "add an index on it".
Optimizing Search Query For Millions Of Rows
I have mysql 4.1 and Im having a difficult time optimizing this query. select domain, length(domain) as len from domains where length(domain) <= ཌ' and not (domain regexp '[[:digit:]]') and domain not like '%-%' and price > Ɔ' and price < ཌ' and end > ��-12-01' order by end ASC, len ASC The following query outputs: | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | ------------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | domains | ALL | end | NULL | NULL | NULL | 2600000 | Extra where; Using filesort | My indexes are: ID - PRIMARY, Unique domain - Unique end Is there anyway this query could be optimized anymore? With only 2.6 million rows its taking a 5 or 6 seconds. It looks like its not finding the right keys.
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?
Help With Mass Update Query
I have a field in my table (1000 records) called company_number. I've just been informed that all records that start with "1" should actually start with "0". So, for a record where compnay_number = 1000400 the number should actually be 01000400. Can anyone suggest how I would approach updating each record via one SQL statement? SELECT * FROM `myTable` WHERE `company_number` LIKE '%100%' ... then what?
Update Value Directly In The Same Query
Is it possible to update a value directly, adding text to it, without getting it first, adding the text in php and then do the update. for example: value in field users: ",45," and then adding ,34, to the field directly by doing something like: update table set users='users,34,' so the value in the field afterwards will be ",45,,34,"?
Embed Sum() Within Update Query
I'm currently rewriting my website to work with MySql rather than MSSQL and have stumbled across a bit of a problem. Can anyone help? To create a simple example of what I'm trying to achieve - I have 2 tables. I wish to populate the SCORE in TABLEA with a sum of all SCORES in TABLEB where the CODE matches. TABLEA has a single row for each CODE but TABLEB may contain multiple rows (for different weeks).....
Maximum Query Update?
Is there a maximum amount of fields that you can update at once as run from php the following works: UPDATE `members` SET `firstname` = '$formfirstname', `surname` = '$formsurname', `usrlevel` = '$formusrlevel', `usrname` = '$formusrname', `grade` = '$formgrade', `phone` = '$formphone', `email` = '$formemail', `comments` = '$formcomments' WHERE `ID` = '1' LIMIT 1 ; Yet the following does not and it only has one extra field to update: UPDATE `members` SET `firstname` = '$formfirstname', `surname` = '$formsurname', `usrlevel` = '$formusrlevel', `usrname` = '$formusrname', `grade` = '$formgrade', `phone` = '$formphone', `email` = '$formemail', `drivingdate` = '$formdrivingdate' `comments` = '$formcomments' WHERE `ID` = '1' LIMIT 1 ;
Update Query Between Two Tables
I am having a problem with getting an update query to work. here's what I am using: Update stck Set vendor = 'items.vendor' where prtn = 'items.prtn' I am getting a items table not found error. I thought this is what I needed to do to update many records from one table to another.
SELECT And UPDATE In One Query
Does there exist a way to do a SELECT and an UPDATE in one query? What I am trying to do is building a shop that´s articles are saved in a mySQL db and I wanna count the impressions of each article that is shown by e.g. a search function. would be something like that: article no | text | category | impressions 1 | aaaa| 1 |1 2 | bdsd| 2 |1 3 | aaaa| 2 |1 4 | bdsd| 3 |1 and so on now I want e.g. to search for all articles that are in category 2 AND add +1 to the impressions cell of those articles that are found. I hope it´s understandable what I am trying to do. I guess it should be possible, but since I only so far used very simple queries I am not sure how to?
Multiple Update In 1 Query
I am trying to do the following using PHP and Mysql: $sql=" UPDATE records set appear_order='2' WHERE id='19'; UPDATE records set appear_order='3' WHERE id='17'; UPDATE records set appear_order='4' WHERE id='18'; UPDATE records set appear_order='5' WHERE id='20'; UPDATE records set appear_order='6' WHERE id='16'; "; I am getting the following error message: " Cannot update records: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE records set appear_order='2' WHERE id='19'; UPDATE recor" I don't know why this isn't working. when I paste this query to phpMyAdmin (I hope you guys know phpMyAdmin) - it works. somehow using a PHP code it does'nt - I get that error message. did anyone every come across this error and knows how to overcome it?
Msql Update Query
I am trying to tie a mySQL UPDATE query to a variable in php to determine whether or not the query updated any rows or not. All it returns is whether or not the query was successfully executed. I have tried using the mysql_affected_rows() command, but as there are multiple users performing the same action this could lead to ambiguous results.
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.
Slight Problem With Update Query, Please Help!
I have the following query, which I can't seem to make work. I want to count the values of one table and insert them into the mema table. How can I do this. Here is the query: Quote: mysql_query("UPDATE mema set usalistings='(SELECT count(mem) FROM mema, articles WHERE mema.ided=articles.mem AND mem!=0 group by mem)'");
Quick And Easy Update Query? Or Not Possible?
I need to do a global change to a column in my database. I know this is poor planning on my part, but I recently made a change to all of my image extensions for speed purposes. I have a column called thumbnail_path, and every value ends with .GIF. I would like to change them to all end in .JPG. How can I do this? Is this even possible to do in one update query? or will I need to select, parse w/ php and then update?
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")
QUERY One Table And Update Another In ONE Statement?
ZIP_CODE: zip_code latitude longitude customers: customer_id ... zip_code latitude longitude The lat and long in the customers table is blank. I want to lookup customers' zip code in the ZIP_CODE, get the related lat & long and update the customers table with that information. The result being that after this query is run, the customers table now has lat & long data.
How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work) $sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";
Optimize UPDATE Query With Subquery
Users(users_id,...,pictures_average_grade) - 5,000 rows Pictures(..,average_grade,users_fk) - 50,000 rows One User can have many pictures. Each picture have average_grade which is being continually changed. Once a day i would like to compute for each user average grade of his pictures and update column. users.pictures_average_grade. I wrote the following query: UPDATE users AS u SET u.pictures_average_grade= (SELECT AVG(average_grade) FROM pictures WHERE users_fk=u.users_id). The problem is that the query is being processing very long (after 15 minutes i resing and stop the query).
Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key. In normal conditions, the position of this records should be as follows: 1, 2, 3, 4, 5, 6, 7, 8, 9 . etc... One of my utitities is meant to check the consistency of the table - one of the things it has to do is scan the table as follows: SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record. At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this: UPDATE employe_records SET position = updatedValue FROM employe_records WHERE employe_id = ? ORDER BY position This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.
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.
How Can I Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key. In normal conditions, the position of this records should be as follows: 1, 2, 3, 4, 5, 6, 7, 8, 9 .etc... One of my utitities is meant to check the consistency of the table - one of the things it has to do is scan the table as follows: SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record. At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this: UPDATE employe_records SET position = updatedValue FROM employe_records WHERE employe_id = ? ORDER BY position This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.
Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table. I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded. update boxes b inner join messages m on b.ctnnumber = m.ctnno set b.consigneerecv = m.CName, b.consigneerecvdate = m.DateRcv, b.phrecventered = "Y", b.PhilStatus = "delivered", b.prevreleasestatus = b.releasestatus, b.releasestatus = "delivered", b.PhilStatusDate = m.smsrecvdate, b.phdelprice = "0.00", b.phdelamt = "0.00", b.recvrelation = m.Relation, b.APRecventered = m.smsRecvDate where b.consigneerecv = '' or b.consigneerecv = 'NA' or b.consigneerecv is null; I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this: Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1), table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....
Update Query On 14000 Records
Haing moved a large site from asp and access to php and mysql, I would like to change the way of my fields is delimited. Currently, the field contains user selection seperated by commas e.g. selection1, selection2, selection3 I would like to update this field on every row in the table and change all commas to pipes e.g. selection1|selection2|selection3 The table contains over 14000 records, and the field in question can contain anything up to 30 selections. Is this possible to do?
Slight Problem With Update Query
I have the following query, which I can't seem to make work. I want to count the values of one table and insert them into the mema table. How can I do this. Here is the query: Quote: mysql_query("UPDATE mema set usalistings='(SELECT count(mem) FROM mema, articles WHERE mema.ided=articles.mem AND mem!=0 group by mem)'")
Optimizing My.cnf ?
I have got a server with 320MB ram. This is a free forum hosting server. Many people create own forum every day. So apache+mysql are very important for me. Could you help me with optimizing my.cnf for my MySQL 4.1.14 server ?
Optimizing
I have a strange performace problem, I can't seem to understand. It's the follownig query: PHP Code: select tblrelations.ID from tblcontracts inner join tblrelations on (tblcontracts.relation_id=tblrelations.ID) where (tblrelations.ID='106512' or tblrelations.mainrelationid='106512') order by tblcontracts.ID desc
|
TRACKER
