Optimize UPDATE Query With Subquery

Update With Subquery?
I have two tables and I want to update one with data from the other

TableA
id description date

TableB
id counter date

I want to update counter in TableB with its current value minus a count from tableA

UPDATE TableB set counter = counter - (SELECT count(id) WHERE description = 'blabla')

But I only want to do this on the date columns are equal. For example date in TableB is 2007-03-13, and counter should be updated with its value minus the selection from TableA where date is the same. But I want to do this on all rows for all dates in one query. How do I do that? Add a where to the subquery ie "WHERE TableB.date = TableA.date"? Is that correct?

---

How Can I Optimize This Query?
tables:
---------
branch:
PK = branch_ID
(has many)
galleries:
PK = gallery_ID
FK = branch_ID
(has many)
photos:
PK = pic_ID
FK = gallery_ID

query:

select x,y,z
from galleries join photos
on photos.gallery_ID = galleries.gallery_ID
where galleries.branch_ID = 1
order by photos.img_weekHits desc,photos.img_hits desc
limit 0,1;
explain says the following:
---------
table: galleries
type: simple
possible keys:3
ref: const
rows: 200
extra: Using where; Using temporary; Using filesort
---------
type: simple
table: photos
ref: galleries.gallery_ID
rows: 35
extra: Using where

I need to run this query for each branch of my website (currently there are 7). the query gets the most popular picture for the week from that branch.

All fields used in where, joins and order by are indexed.

The photos table has 40 000+ rows in it.
The galleries table has 500+ rows in it.

The query takes about 3-500ms on my laptop to run for my busiest branch, and less for less busy branches.

The page that runs the query is cached, so the query is only run every half hour or when new content is added or the cache is flushed.

Is there any better way i can run this query, or alternatively achieve the same result with putting less load on the server?

---

Optimize Query
What indexes are required to speed up the following query.

SELECT COUNT(*) AS cnt FROM company WHERE state = 'Maharashtra'
AND city = 'MUMBAI' AND companyName REGEXP '^D' GROUP BY parentid;

---

Optimize Query
I'd like to optimize this query:Code:

SELECT *
FROM `links`
WHERE active = "1" AND mainweight != 0 ORDER BY Rand()*(1/mainweight) LIMIT 5

I have a database of links wich has 3 000 rows. I'd like to select weighted random links from it (mainweight is the weight of the link). On my serwer this query is executing 0.8 sec. When i have 50-100 users on my site this is a problem.

---

I Need Help With A Query/subquery Tangle
I started out trying to do everything at once and really ran into problems (5 tables! my head hurts), so i broke the original query in half to get:

$query ="SELECT
members.id,
members.lname,
members.fname,
member_email.email,
member_phone.phone,
committees.id,
committees.name,
committees.description,
committees.chair_id,
committees.staff_id,
staff.phone,
staff.email,
staff.id,
staff.name
FROM committees, staff, members, member_email, member_phone
WHERE committees.name='Test'
AND members.email_id=member_email.id
AND members.phone_id=member_phone.id
AND staff.id=committees.staff_id
AND members.id=committees.chair_id";

//ORDER BY members.lname DESC
$result= mysql_query($query)
    or die ("could not retrieve information"); ............

---

How To Rewrite This Query Without A Subquery ?
For speed reasons, I would like to rewrite the following query without the subquery: ...

---

Need To Use Same Table In Subquery As In Insert Query
I have this table called "testtab":

ID | value
----------
1 | 8
2 | 15
3 | 5

Field ID is INT autoincrement as usual for ID field, field value is common INT. I need to insert new record to it. The inserted value should be equal to the highest value +1. I tried to do something like this:

INSERT INTO testtab SET value=(select max(value) from testtab)+1
Mysql threw this error:

You can't specify target table for update in FROM clause
I searched internet and found that I cannot use the same table in a subqery of insert/delete/update query. So my question is: Is here some workaround for this? I know I could use two queries - one to find the maximum value and second to insert the value+1 into DB, but I would like to do it in a single query.

---

Question W/ SELECT Query - Need A Subquery?

I have 2 tables:
*user - each row is a user
*interest - each row is a relation between a user and an interest

I am trying to construct a query to determine all other users that share overlapping interests; and also report the total number of interests these users have. I am having diffculty extracting the part in bold.

The following query will extract everything, but the part in bold:

Quote:

SELECT DISTINCTROW *, count(u.userid) AS matches
FROM interest f1, user u
WHERE f1.userid=u.userid AND f1.interestID IN ($qstr)
GROUP BY f1.userid
ORDER BY matches DESC
LIMIT 0,10

note that $qstr contains a list of all interest IDs that the reported users must share.

I have now built the following query, which doesn't work:

Quote:

SELECT DISTINCTROW *, count(u.userid) AS matches, count(f2.interestID) AS denom
FROM interest f1, user u, interest f2
WHERE f1.userid=u.userid AND f1.interestID IN ($qstr) AND f1.userid=f2.userid
GROUP BY f1.userid, f2.userid
ORDER BY matches DESC, denom DESC
LIMIT 0,1

---

Query With Row Being Used To Identify Database In A Subquery
- FOODS -
id | food
1 | taco
2 | burrito

and the following tables named taco and burrito

- TACO -
id | food_name
1 | soft taco
2 | hard taco

- BURRITO -
id | food_name
1 | fried burrito
2 | super good burrito

I want to be able to create a query that will select the id from foods and then use the food row to determine which database to use in a subquery.

select id, (select food_name from taco.my_food) from foods;

The taco part that is red in the query above is where i need to pull the reference from the foods table in the food row.

(please keep in mind this is a super generalized version of the query, so there are no where statements,

---

Can 2 Queries Be Condensed Into 1 Query Using A Subquery Or Join?
I am developing an image gallery, and storing information about the hierarchical folder structure in which the images are organised using a modified preorder tree traversal method. I have 2 main tables:

---

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.

---

Subquery Or Correlated Subquery Help
I need to develop a sql that uses the results from the first Query to find data in the second Query. Then the results of the second query to find the final results of the third Query. I’m also wondering if I should try to just link all these tables together instead of Subqueries or Correlated Query.

First Query
select

ACCOUNT_ID,
ACCOUNT_TYPE_C,
PAT_ID

from PAT_ACCT_CVG

where ACCOUNT_TYPE_C in (120103,120104,120101)

Second Query

SELECT

PAT_CVG_FILE_ORDER.PAT_ID,
PAT_CVG_FILE_ORDER.LINE,
COVERAGE.COVERAGE_ID,
COVERAGE.CVG_EFF_DT,
COVERAGE.CVG_TERM_DT


FROMPAT_CVG_FILE_ORDER
LEFT OUTER JOIN COVERAGE
ONCOVERAGE.COVERAGE_ID = PAT_CVG_FILE_ORDER.COVERAGE_ID


Where coverage.payor_id = ?'


Third Query

select

TRAN.ORIG_SERVICE_DATE
TRAN.TRAN_TYPE,
TRAN.INSURANCE_AMOUNT

from Tran

where TRAN.TRAN_TYPE = 1
and TRAN.INSURANCE_AMOUNT > 0
and TRAN.proc_ID in 1008,1009

(now I need to compare the dates on this query to make sure that the TRAN.ORIG_SERVICE_DATE is within the COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT ( dates of the second query)

---

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=&#391;'
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 &#55613;&#56984;'
SET type = &#395;'
WHERE type = &#394;'

---

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 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 &#390;',
`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 &#390;',
`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.

---

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.

---

What Does OPTIMIZE Do
I use phpMyAdmin and constantly see the OPTIMIZE TABLE option on the structure page with a number of bytes highlighted. You press the button and the bytes are gone! Great!!!

But what exactly did that just delete? Should you optimize or just leave the databases as they are? Is there a chance of corrupting the data in a table when you run this?

---

Optimize This Sql
optimize this sql

SELECT DISTINCT(ibf_posts.author_id), ibf_topics.* FROM ibf_topics LEFT JOIN ibf_posts ON (ibf_topics.tid=ibf_posts.topic_id AND ibf_posts.author_id=25869) WHERE ibf_topics.forum_id=4 and ibf_topics.approved=1 and (ibf_topics.pinned=1 or ibf_topics.last_post > 0) ORDER BY pinned DESC, last_post DESC LIMIT 0,30 .

---

Optimize
the following query takes forever on 80,000 records. I was wondering if there was anyone that sees how I could speed this query up.
SELECT c.products_model AS model, SUBSTRING(warehouse_sku FROM 4) AS sku, a.products_id AS products_id, products_viewed
FROM products_description AS a, products AS b, products_to_warehouses AS c
WHERE a.products_id = b.products_id
AND b.products_model=c.products_model
AND warehouse_id = 2
ORDER BY products_viewed DESC;

---

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.

---

Update Query Not Working: SQL Error
i'm trying to do an update such as ...

---

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'";

---

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.

---