MYSQL :: Very Slow Table Update

Slow UPDATE
I have a table with the following structure;

CREATE TABLE my_table
(
id_1 int(11) NOT NULL ,
id_2 int(10) NOT NULL ,
stauts tinyint(1) NOT NULL DEFAULT 0 ,
PRIMARY KEY (id_1)
) Engine =InnoDB';

The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query;

UPDATE IGNORE my_table
SET id_1 = 74240, id_2 = 5

I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.

Slow Update
The following query can sometimes take up to 2.5 seconds to execute on a table with only 150,000 records.
UPDATE items SET item_views = item_views + 1 WHERE id = 5897;
is there any way I could speed this up? Some setting I could change to make MySQL faster for this?
The field "id" is the primary table key.

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 Function Very Slow??
I have a large table (77,321 Rows) and I'm trying to update it. For some reason, UPDATE takes a long time. Maybe it's my query? I'm doing it through php, maybe that's it? I'm running the program locally using the CLI, and it still takes ages.

Should I not expect it to be as fast as SELECT functions?

$update ="UPDATE `ch_products` SET `products_weight` = '".$IDS[$i][Weight]."' WHERE `xref1` = '".$IDS[$i][ID]."'";

How can I make this faster?

Very Slow Update Statement
I am having an issue with an UPDATE statement that takes a very long time. I am using
1 table in a schema to update another table in another schema. Below are the create statements and the update statment I am using. Table and column names have been changed to protect the innocent :) Code:

Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do:

There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns.

The most common query is SELECT data FROM table WHERE n1 = ?

The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column.

Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset:

SELECT COUNT(1) AS total FROM table WHERE n1 = 3;
index = random number from 1 to total
SELECT data FROM table WHERE n1 = 3 LIMIT index,1;

How can I speed this up? I need it to take less than half a second if possible. Thank you.

Slow Table Access On Local Machine
I'm running MySQL 4.1.7-nt on my laptop, and I'm accessing it through ODBC
(MyODBC-3.51.10-x86-win-32bit).

I'm developing a .NET web application, but I don't think that's relevant.

I already had the application running OK on my desktop, and when I created the
same environment on the laptop, retrieving trivial amounts of data (5 rows) from
a table is taking 6 seconds. Updates the same. IN otherwords any table access
takes 5 or six seconds. (Table only has about twenty rows in it).

I can connect to the database on the laptop from the desktop (i.e - application
runs on desktop, retrieves data from laptop - works fine, not noticeably slower
than local on desktop.

If I connect the other way - application on laptop, database on desktop, it runs
just as slowly as locally.

The effect is the same whether I connect to 127.0.0.1 or via the machine name. Code:

Why LAN Clients Frozes While A Slow Networked Client Downloads A Big Table
With MySQL 4.1 I think from a day to another something changed in the working of our server.

When a remote agent with very slow Internet connection downloads 60.000 rows query lets say downloads the whole "products" table, all of the other fast LAN clients stop responding until the big download finishes. It is possible, that they would like to change the "products" table while the remote client downloads it...

But! There is no lock to the table, just its a simple "SELECT a,b,c,d FROM table ORDER BY a" query..

Can it happen because of that the table becomes bigger and bigger, and now MySQL can't copy it to in the RAM, and let others live, and now it stops other clients instead?

Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day.

This is my query:

select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable
group by mydate

It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....

Update Column In Table A With The Count Of Another Table
Having my SQL-background in Oracle I'm looking for the MySQL equivalent for:

update tableA
set column1=(select count(*) from tableB where tableA.key= tableB.key)

cannot find anything similar in the manual, but probably overlooking
something.

Update One Table Value With Values From Another Table
I am trying to update one table value with values from another table, and I cannot get it to work. What am I doing wrong?

This is my SQL-command:
UPDATE tabel1 SET tabel1.name=tabel2.name WHERE tabel1.ID=tabel2.ID

Update Table A For Every Instance In Table B
I have learned how to select records from two tables but now, I need to update every record in table A based on keys found in table B.

I have a KEY which I can match to records in table B and table B has a field which I can match to records in table A. I need to update table A and possibly table B.

How do I go about this?

Update Table With Info In Other Table
i have a case like this:

table table1 (key, accumulator)

table table2 (key, counter)

i want to, for each table1.key = table2.key, update accumulator with the info in counter, something like this:

update table1 set accumulator=accumulator+table2.counter where (? counter is the value related with the same key as the one in table1)

how do you write a sentence like this one?

Update Table From Temporary Table
I have an import process that creates a temporary table and then dumps a large amount of records into it. I need to update an existing table against this temporary table. So where the record exists I either want to update or replace (probably based on parameter but can be a separate query for each) it in the live table, and if it does not exist i want to add it.

The temporary table can contain a large number of records (100000-200000). How can I create a fast and efficient process for doing this? Is there a query I could write to do this or would I have to do this through through code (i'm working in a java environment). I think a query would be faster than processing that many records via jdbc.

Update Table With API
I'm trying to develope a getway between matlab and mysql. I would like to
write the result of matlab routine into mysql table without "UPDATE
.....SET..." statement, because I've have to write a different value for each
row and I have to write a lot of row. I would like to write table row by row
sequentially.

UPDATE Table SET
Does anyone know how to implement this into a php form CORRECTLY? I have all the proper syntax but then the server gives me a message that says the mysql version may not go along with the syntax. How do I work around this then? I need to update query strings and such and nothing will work.

Update From One Table To Another ????
I have two tables with linked fields, a one to many relationship. It is a item sold table to work order table where there are many items sold linked to a single workorder. I want to add up all the item taxes for the particluar workorder and instert it into the appropriate workorder. I have tried something like this:

update set workorder.tax (sum)woitem.tax where workorder.id=woitem.linkid

Can someone show me where I am going wrong?

Table Update
How do I relate such a way that I update a column of the main table, another secondary table's column related to the main table gets updated automatically?

UPDATE From One Table To Another
This should be so simple but I'm getting a very strange error??

I'm using MySQL version: 4.1.12-log

I've got an outdated country table that I'd like to update info from an ISO table. Pretty straight forward if you ask me.

Here's the query:
UPDATE country, isocountry SET country.un_numcode=isocountry.numcode
WHERE country.country_code=isocountry.iso;

I created a column called un_numcode and then I'd like to put the iso numcode info in my table where the country_code = iso code (same data, different field names in different tables).

It goes along really well for 38 rows out of 239 rows?? And then I start getting 127 in ALL the un_numcode fields even though that value does NOT exist in the country.numcode table!

Update Table
Im trying to update a table, but Im getting the following error...
Syntax error or access violation: 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 '' at line 5

Here's my code...

<cfquery>
UPDATE printers SET name= 
<cfif IsDefined("FORM.name") AND #FORM.name# NEQ "">
'#FORM.name#'
<cfelse>
NULL
</cfif>
WHERE printerid=#FORM.printerid#
</cfquery>

Update Table
What is the right syntax of UPDATE command, I want to use multiple (more than 5) WHERE clauses and update more than 2 fields.
UPDATE table SET UPDATE1 =â€™xyzâ€™ UPDATE2 = â€˜abcâ€™ WHERE 1=â€˜zxcâ€™ 2=â€™werâ€™ â€¦â€¦â€¦â€¦?

Update Table
I want to update a number in a column adding a 1 to the current number, however I don't want to select the table first.

Update Table
everytime it tried to update row in any table, it takes very longtime, it locks the table, then i run out of connections and mysql crashes.

2-table UPDATE
I am running the following query through PHP's mysql_query:

UPDATE hotel, hotel_brand
SET hotel.hotel_brand_id=0, hotel_brand.hotel_brand_parent_id=0
WHERE hotel.hotel_brand_id=6 AND hotel_brand.hotel_brand_parent_id=6

In actuality, it's two queries combined into 1. I am deleting a value that rows in these 2 tables reference, and want to set the values to 0.
Of course, the problem is the values aren't changing to 0.

Is splitting these up the best solution?

Update One Table
I have 2 tables one for the team standings one for the scores, this select statement will calculate win loss :

SELECT
SUM((homescore>awayscore and home=teamid)
OR (awayscore>homescore and away=teamid)
) as wins,
SUM((homescore>awayscore and away=teamid)
OR (awayscore>homescore and home=teamid)
) as losses
FROM scores, teams
order by teamID

UPDATE Table
Code:

UPDATE table SET field='select concat(field1,field2) from table'

This gives me a zero in my field
I can select concat separately without any problem

Using Import To Update Table
I'm working on a business directory site that I created using phpmydirectory. All of my data was imported into mysql from the admin area of my site.

My category table has ID, Title, Description, and Keywords fields. At the time that I imported my business listings, I left the Description and Keywords fields blank. I am now wanting to go in and add descriptions and keywords to each category. I would like to setup a simple spreadsheet and fill in the description and keywords for each category, and then use Import in PhpMyAdmin. Is this possible? If so, how would I set it up? Am I making sense? I really have no idea on how to use PhpMyAdmin. I can browse around and export when I need to, but I'm scared I'll screw something up if I try to update a table.

Update A Table Being Selected?
Is there a relatively easy way to fix this bad decision I made long ago? I created a table thusly:

CREATE TABLE `blah` (
`mod_date` date NOT NULL default &#55612;&#57200;-00-00',
`mod_time` time NOT NULL default ༼:00:00',
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I added a new field to the table that looks like:

`modified` datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00'
I thought I could update it with:

UPDATE blah
SET modified=(SELECT CONCAT(mod_date, ' ', mod_time) FROM contacts);
But a search in this forum showed that one can't UPDATE a table that is being SELECTed from. There was mention of a temp table (and perhaps JOIN(?)), but further searching has left me wanting.

UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second
table? I've deleted rows in the second table, which has a unique
auto_increment key. The first table now has references to keys that no
longer exist. I need to update the first table, setting the value to
NULL where the referenced key no longer exists. Something like:

UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN
table2.ID;

The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go
there.

Update Based On Other Table
I know this is how this query would be written in SQL but I think something isn't working correctly since this is mySQL.

SQL query:
UPDATE watches SET watches.totalhits = ( SELECT COUNT( id ) AS totalhits
FROM watchtracker
WHERE watches.id = watchtracker.watchid )

MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(id) as totalhits
FROM watchtracker
WHERE watches

Update A Table With Values In Another
I have the following table

tableA
column_a
column_x
column_y
column_z

tableB
column_x
column_y
column_z

How do I update the rows of tableA that match rows of tableB (all rows
of tableB are unique)

What I want to acheive is a check against tableA.column_x with
tableB.column_x, if they match, then update the row in tableA.column_y
with the value of tableB.column_y and tableA.column_z with
tableB.column_z

Update Table Problems
I'm trying to update a table where specific values are in another table. Here is the update statement I am using:

UPDATE Route_Detail,Routes SET Route_Detail.Trip_Calc='S'
WHERE Routes.Route_ID = Route_Detail.Route_ID
AND Routes.Project_Code = 'OSUT'
AND DATE_FORMAT( Route_Detail.R_Date, '%m%Y' ) = 072006

This statement runs but does not update any rows.

This select statement returns 11 rows:
SELECT RDetail_ID
FROM Route_Detail
INNER JOIN Routes ON Routes.Route_ID = Route_Detail.Route_ID
WHERE Routes.Project_Code = 'OSUT'
AND DATE_FORMAT( Route_Detail.R_Date, '%m%Y' ) = 072006

How do I get the UPDATE statement to update those 11 rows.

I'm using MySQL 4.1.21-standard version.

Update Table By ID Ranges
How do i use the UPDATE statement to update a range of Primary Key id
numbers. (let say 100 through 1000).

Example,

UPDATE testTable set testField='9999' WHERE
test_id in (100:1000);

Fields In Table Will Not Always Update,
I'm using C#, ASP.NET and MySQL, The language is no problem (not to much) but the rest well I wonder. I have a DB table that has 5 seperate fields. A main one that
is Integer and the rest are VarChar(50). each of the 4 others are identical in setup. I can change some records and others I can't.

ID int(10),
std varchr(50),
atd varchr(50),
dtd varchr(50),
ctd varchr(50)

I update say 'atd' with new data, Date, Time and a code of 15 letters/numbers.This one takes, I change to another record and try to update ctd and it shows like it takes but when I use the Command Line it shows no updates on ctd. this is random on this also.
If I change to another record it may all work or not. Any Ideas at all?

Update Fields In Table
I am trying to update multiple records,rows,fields in a table. Below is what I am placing in the sql field in phpmyadmin. It works with one update line but not more. What am I missing here?

UPDATE Products SET TitleTag='U.S. Air Force Retired Shop On-Line for Flags of the world - US, International Flags ' Where Product_ID=28;

UPDATE Products SET TitleTag='Vietnam Veterans of America Shop On-Line for Flags of the world - US, International Flags ' Where Product_ID=29;

Update Table Values
I have a csv.txt file with contacts and their information in it. Then I also have a table in mysql with the same contacts and their various information. I need to merge these to so that if a field value in the database is NULL the value that is in the csv.txt file is inserted into the database field.

I have been looking at the mySQL site on how to do this but have not been able to produce a working solution. Below is a more detailed example of what I am looking for. Code:

UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second
table? I've deleted rows in the second table, which has a unique
auto_increment key.

The first table now has references to keys that no
longer exist. I need to update the first table, setting the value to
NULL where the referenced key no longer exists. Something like:

UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN
table2.ID;

The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go
there.

UPDATE From A Linked Table
I wanna update the table COMPANY from a linked field. The following is not
accepted. Could anybody help me with what the command should be?
Thanks a lot.

UPDATE `COMPANY`
SET `COMPANY`.Region_ID =
(
SELECT `Area`.Region_ID
FROM `AREA`
WHERE `COMPANY`.Area_ID = `Area`.Area_ID
)

Multiple-table Update With Sum()
I have the quantities from different warehouses for each product in products_quantities, and the aggregate quantity for each product in products. I want to update products with the total from products_quantities. Why does this not work?

UPDATE products p, products_quantities pq
SET p.products_quantity = SUM(pq.quantity)
GROUP BY pq.products_id

I've tried every variation I can think of, but always get a syntax error near 'GROUP BY pq.products_id'. Can I not update one table with the sum of values from another table, in one query? I'm using 4.0.26.

Update Same Table With More Than One Record
I have a forum table which I need to perform the following update

1) I have an original message which I need to change its online status into DISABLE 'D' in my "status" field as follow single command:

UPDATE forum SET status = 'D' where message_id = 1 and parent_id is NULL;

*the parent_id field is NULL because it is a parent thread...

However, it has some child threads to this message...therefore I must force the all the child thread to change the status into 'D' also. So I did a second sql query as follow:

UPDATE forum SET status = 'D' where parent_id = 1;

*parent_id = 1 means it points to the parent message_id = 1

My question is

DOES anyone know a good sql query that will combine these two queries into ONE single query?

Update Linked Table
ODBC --update on a linked table 'tblname' failed.
[Microsoft][ODBC Driver Manager] SQL data type out of range (#0)
I get this message whenever I try and update or change anything. I can't even open a table, delete a number 4, replace it with 4, and it still fails.

What causes this? I have a primary in every table (though they aren't called primary) and a timestamp in each table.

Update Table With Java
im trying to update my table bunt

int test=5;
String update="Update bunt SET buntnr='test' Where bunt_ID ='ver.bunt_ID'";

wich results in this error

Data truncation: Out of range value adjusted for column 'buntnr' at row 1

If i use this, changing variables to Ãnt it works fine!
String update="Update bunt SET buntnr=5 Where bunt_ID =1";

Update Records In Table
i have installed a mysql server, and i have a problem with import record in database.
the origine is a file txt or csv, the destination database contain also other records, and i must update the records into database

Multiple-table UPDATE.
I'm trying to update multiple-tables at the same time but I read somewhere that you could do it if you had mysql version 4.0.4. But currently, I have version 4.0.0 and I want to know how to do it with this version

Insert And Update Entire Table
My website will modify records on my products table. But I need to occasionally update their qty and their prices without losing any changes already made by other users online (like qty sold).

I'm able to do that now. It works really well:

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.