MYSQL :: Calculate A Moving Average And Updating A Table With It

Moving From Access; 20 - 200 Record Db's Updating To One Master Db
I have a simple coldfusion db web app that is running off an Access db. It would have 200 to 300 records at the most with about 20 text fields and 40 checkbox fields. I would like to move it over to mySQL. This web app will be going on about 20 web sites in the next year and I want each web site to be able to push there record edits, adds & deletes to a master mySQL db.

The app is a car dealer inventory display app.
Is this pretty simple to do. Is it better to have all the different sites working off one db or seperate db's on the same server? Give me some feed back on this one please.

Average Query With 2 Rows From Same Table
My table:
"answer"
answerID
answer(int)
questionID(int)
userID(int)

answer1 is questionID = 1
answer2 is questionID = 2
WHERE userID is the same for both answer1 and answer2

I want the average of answer1/answer2: AVG(ans1/ans2), but how?

How Do I Calculate A New Value From 2 Columns Whilst Looping A Table
I have a single table and i would like to calculate two columns (imp and clicks) while looping through the results.

this is what the table would look like...

campaign | imp    | clicks
A        | 200000 | 100
B        | 100000 | 40
C        | 50000  | 10
D        | 300000 | 200

and here's what i would like to achieve with a mysql query and php. I would like to create a new column CTR% and calculate the CTR for each row by looping through the table...

campaign | imp    | clicks | CTR %
A        | 200000 | 100    | 0.05
B        | 100000 | 40     | 0.04
C        | 50000  | 10     | 0.02
D        | 300000 | 200    | 0.06

i've tried a few things that i've read up on by googling but none really worked (based on using a while loop). now i would like to wipe the slate clean and get your suggestions on what to do??

Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary)

I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's.
I need to do this several times and have tried it several ways to no avail.

Table A
---------
companyID int(10) pKey
legacyID int(10) old legacy pkey

Table B
---------
bAID int(10) pkey
companyID int(10)
legacyID int(10)

Table A has values for both companyID (unique key) and legacyID.
Table B has values for bAID (unique key) and legacyID but companyID is empty.

I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship.

I need a query that will update ALL rows.

Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file.
Each record has a number of fields, including the authors name. I'm
trying to convert the database to something a little more efficient.
I've created a new table (called Authors) of unique authors names and
assigned each one a unique ID. I've added a new field in the original
table (called Books) for the author's ID. Now, I need to update the
original table with the author ID from the Author's table.

Something like this:

UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName =
Authors.AuthorName

This obviously doesn't work. Any assistance on how to forumulate this
query (or, if I'm headed down the wrong path, the correct way to do this
operation) greatly appreciated.

Moving Table From One Server To Another
I have a table with data on my mysql database (ver 3.1) I wish to shift the data to an external server. I dont know the version of the external server

My web provider just provides me an interface to change the password. How am i supposed to shift the table with the data.

Moving Data From One Table Into Another
I have a users table that has 20 columns (table A). I want to be able to take half of those columns and move them into another table (table B), and use B as a lookup.

Table A (currently)
user_id
fname
lname
ip_address
date_added
I want to make it like this:

Table A
user_id (PK)
fname
lname

Table B
tbl_A_user_id (FK)
ip_address
date_added
The problem is that I have some clients using versions on their site, and need to be able to move the data from A into B, then drop the columns in A without losing data.

Moving Records From 1 Table To Another
Is there and easy SQL syntax to move or copy rows from 1 table to another?

I'm creating a temporary table and after matching duplicates, and deleting the duplicate rows, i want to move or copy the remaining rows into the permanent table.

Moving Data In Same Table
In the same table, TableOne, I'd like to move information from FieldOne to FieldFour where DoA is null.

Moving Table Data Between Databases
I've recently got a 2 new domains and with that 2 new databases. The name of one of the new databases is different from the database on my old host and I have 2 sites incorporated into that old database. What I'd like to do is split the old database into 2 newly named databases seperating the tables so I end up with the 2 databases ready for the new sites.

The old site doesn't have phpmyadmin installed which is what I'm used to using and this DBTools software doesn't appear to allow me to select the tables I want to dump and export to a new .sql file in multiples.

Updating One Table With Another
My table contains a product number field 'packCode' but some clients may have their own internal product code. I have created a table ('configurators') which contains our product code along with two columns each for clients: their product code and price, i.e.:

packCode, insight_code, insight_price

The client sends me data containing our pack code matched against their product code and price. I wish to merge their data with our price list in the configurators table. The MySQL I have used is:

update configurators
set insight_code=iCode, insight_price=iPrice
where configurators.packCode=config_insight.iPackCode;

(The columns from the client data are preceded with 'i', i.e. 'iCode', 'iPrice' and 'iPackCode')

However, when I run the query, I get the unusual error

"ERROR 1109 (42S02): Unknown table 'config_insight' in where clause"

suggesting that the table 'config_insight' does not exist, which it clearly does. Is this the correct way to merge data from one table into another? Is my SQL query valid or am I missing something?

Updating Table
I'm sure this is possible, probably with a sub-query.I want to update a record with a value from another record in the same table.

E.G.
CODEName     Val_1     Val_2    Check_col
Test1    0         0        s
Test1    10        20       M
Test2    0         0        s
Test2    20        25       M

Updating Table
I have a database table and 1 column has positive and negative values in it.

I want to update the table so that all entries in this column are positive... so all negative values like -3, -2, and -1 become 3,2 and 1 respectively in the table.

Updating Table
I have a database of books that was originally created as a flat file.
Each record has a number of fields, including the authors name. I'm
trying to convert the database to something a little more efficient.
I've created a new table (called Authors) of unique authors names and
assigned each one a unique ID. I've added a new field in the original
table (called Books) for the author's ID. Now, I need to update the
original table with the author ID from the Author's table.

Something like this:

UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName =
Authors.AuthorName .

Updating Table Value
update jobs set paid=true where jobid in (SELECT jobid from jobs where CreatedBYusername='roop')

after executing this query i got an ERROR:

You can't specify target table 'jobs' for update in FROM clause.....
same thing i executed in mssql....how can i do this in mysql

Updating Table
I have two tables:

table1:
id code
1
2
3 aa
4

table2:
id code2
1
2
3 xx
4 zz

I am looking for a way to update table1 with some sort of sql statement. I looked into update and joinn but I couldn't find any relevant information. The reason I don't have any success is prbably because I don't have the correct keyword.

Invision - Moving Members To Validating Table
I am using Invision 2.0.3 and i need help with a query which will move all the members from the members table to the validating table in the database. Reason i want to do it is so i can resend the activation email to all members so they have to reactivate their account.

I tried using this query:
UPDATE ibf_members SET mgroup = 1 WHERE mgroup = 3

This only moves their group and changes their permissions, it doesnt actaully put them into the validating table.

The validating group uses different fields than the members group so i dont know how to insert all my members into it.

Updating Table From File
I have a table and I need to update records in one column. There are 450 records in the table. I know I can insert records to table from file, but is there a way to update records from file?

Updating Table Privileges
Running MySQL 4.1.14 through Yahoo. When I access Privileges, I receive the following notice:

""Warning: Your privilege table structure seem to be older than this MySQL version!
Please run the script mysql_fix_privilege_tables that should be included in your MySQL server distribution to solve this problem!""

The Scripts folder does not include this file, and I do not know how to execute a fix for the above issue. Is there a way around this (i.e., uploading the mysql_fix_privilege_tables.sql script to run it, and if so, where can I obtain it?)

Updating Table With Datetime
I have a datetime field in a table and when I ran an update like so:

UPDATE myTable SET number=2 WHERE ID=50

I had the old datetime information in the table and that got
overwritten when I did the update. Now I am sure there is no way of
getting that back (yup, believe it or not I didn't do a backup) but
just a general question, should i have done the update like so:

UPDATE myTable SET number=2, mydatetimefield=mydatetimefield WHERE
ID=50

to keep the same information in the datetime field??

Updating A Mapping Table
I have a Publications table and a Themes table. There is also a mapping table for these tables as well.

When I input new records into Publications, my mapping does not get updated. When I delete, then the mapping table will get updated. (This is MySQL 4.x, so no trigger statements.)

My fear is that as I use the mapping table to display some information on a Web page, I will need to update that table "by hand" after I've entered a new record for Publications. Is there some method that I don't know of that will update my mapping table whenever I put in new records into Publications (esp. via a Web form)?

This is the setup for the mapping table: it has foreign keys on the primary key (ID) column of both Publications and Themes. That's it, nothing else in there.

Table Data Updating
I've heard that oracle will update data in a table when a specific event occurs and was curious if mySQL will do the same?
For my site I'm designing a comment section that will be attached to my news section. My database organization scheme will consist of two tables. One table that will hold all the news entries and another that holds all the comments. Thus, when browsing the news section visitors initially only access the news table, and once they click on the comments tab then they will access the comments table. Figured this was the best way to organize the data. However, I want the news table to contain the number of comments that exist for that news-section so that the count can be displayed.
My question is how can I make the news table update its data column containing the count for comments when the comments table recieves an update? Is this possible at all in mySQL? If there is a resource someone could point me in let me know, or if anyone has suggestions of a type of hack that would suffice as well

Updating Table With Reference To Another Table
I am trying to update two columns of a table (date and location) so that they are equal to the date and location of another table on a lastname join. Here's is the code I have right now:

update tblA
set tblA.date = tblB.date, tblA.location = tblB.location
where tblA.lastname = tblB.lastname;

I am getting a syntax error with the above code. Does anyone know how to do this?

Updating A Table Based On The Value In Another Table In Ver 3.23.54-Max
I am trying to update fields in table SOLAR_SYSTEM based on the values in RESEARCH_LEVEL table

This is a SQL that I am trying to use

UPDATE SOLAR_SYSTEM JOIN RESEARCH_LEVEL ON SOLAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET
SOLAR_SYSTEM.CASH_METAL=SOLAR_SYSTEM.CASH_METAL+1000,
SOLAR_SYSTEM.CASH_CRISTAL=SOLAR_SYSTEM.CASH_CRISTAL+1000,
SOLAR_SYSTEM.CASH_ENERGY=SOLAR_SYSTEM.CASH_ENERGY+1000
WHERE RESEARCH_LEVEL.TYPE='M' AND RESEARCH_LEVEL.LEVEL=1;

I got error

"ERROR 1064: You have an error in your SQL syntax near 'JOIN RESEARCH_LEVEL ON SO
LAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET
SOLAR_SY' at line 1"

I don't know what is wrong. Can I do such an update in Ver 3.23.54-Max in the first place ? IF yes, then what's wrong with my statement?

Moving A Node In A Preorder Tree Traversal Table
Im going crazy here...how do I move a node in a modified preorder tree traversal table?

The database:
-id
-name
-lft
-rgt

SQL

LOCK TABLE sys_resources WRITE;
SELECT @myLeft := lft, @myRight := rgt FROM sys_resources WHERE id = $id;
SELECT @parentLeft := lft, @parentRight := rgt FROM sys_resources WHERE id = $parentId;
UPDATE sys_resources SET lft = lft + (@myLeft-@parentLeft), rgt = rgt + (@myRight-@parentRight) WHERE lft BETWEEN @parentLeft AND @parentRight;
UPDATE sys_resources SET lft = lft - (@myLeft-@parentLeft), rgt = rgt - (@myRight-@parentRight) WHERE lft BETWEEN @myLeft AND @myRight;
UNLOCK TABLES;
The $id and $parentId are correct.
The problem with this code is that the first UPDATE effects the secund ones WHERE.

Updating A Table To Increment Values
Question, if I want to update all values in a certain column to increment by one, what statement could I use? In quasi-MySQL, this is my pseudocode:

UPDATE table SET fields=[previous_val+1] where [previous_val]>[my_val]

Or do I HAVE to run a SELECT query, increment the value, and THEN Update it?

I was just thinking there'd be an easier way.

Updating Table After Doing Multiple INSERT
I'm currently trying to do the following :

INSERT INTO sometable (userID,otherstuff) select ....blah blah ORDER BY RAND() LIMIT 10;

So I do that to get 10 random entries which works fine, however is there an easy way to then say...

For each of the 10 random userID's just inserted update another-field in another table with matching userID to the ones just inserted?

Table Locking When Updating From DataSet?
I have a bunch of code that updates tables from a VB.Net DataSets.

The original code updated Access2000 tables, I migrated the MDBs to MySql and have converted the data adaptors and command builders to MySqlClient statements.

Does anyone have any code examples to to the table locking using DataSets???

PHP + MySQL Updating All Record In Table
I need a loop that updates 2 fields of all the records in my table.
I will multiply these fields by a value passed as POST to php.
I'm having trouble with mysql_fetch_assoc and mYsql_fetch_array... How do I do this... all i have done so far is selecting the fields from the table and outputting to the browser.
How do I modify the values and update them in the right place?

Updating Column With Sumned Values For Each Record In Table
I have most of the query formulated, but am stuck on the portion where I need to specify which records to update. I want to update all the records in the table. How can I specify the request_id to be the same in both the select and update statement?

Here's the query I'm working on:

Code:

update enhancementrequests2 set
hardBenefitEffortSavings = (SELECT hardBenefitEffortSavings + hardBenefitLaborSavings + hardBenefitHardwareSoftwareSavings
+ hardBenefitOtherSavings AS hbTotal
FROM enhancementrequests where request_id = 1), hardBenefitLaborSavings = NULL, hardBenefitHardwareSoftwareSavings = NULL,
hardBenefitOtherSavings = NULL
where request_id = 1

In my example I have specified that request_id = 1, but I want request_id to match each row of the table in turn. I hope that makes sense.

Moving A Table Into Another Table
I created a database with one table that has all my important data. Then I installed an open source application, 4images, into that same database. Now I need to make the information in the original table MOVE into the right places in the 4images tables.

I am trying to use phpmyadmin, but I can't figure it out.

Here's more info about my data:
It has about 2000 records and about 25 columns. One of the columns is simply an "index" or "key." (I think that is what you call it.) It is a numeration of each record.

If I could just learn to put one of the columns in my original table into the 4images table where it needs to go, I think I could do the others.

Updating Table 2 Table
I would like to update a mysql table1 field4 where id 12 from table2 field5 where id is 14.

Help With Moving Data From One Table Field To Another Table Field
I am a relative newbie at this so would appreciate help - already searched and found and tried several suggestions for similar issues - but nothing quite worked!

I need to move a membership roster to another table in the same db - currently the data is at jos_users1 and it needs to be moved to jos_users. The jos_users1 table has only one field, named email.

The jos_user table has several fields including the email field, and already has data in it - so I don't want to overwrite the table - just upload the additional email addresses into the table.

Best Average
OK. Bit of a maths question (at which I am useless)

Basically i've got a table with avg_rating and users.

avg_rating is the average rating of a specific id and users is that amount of people who have voted. I'm trying to sort the list by the best rating. However, if 50 users have voted an average of 2 for one id but only one user has voted 5 for another id, this id would be 1st on the list.

Is there a way I can sort the table by the best avg compared to the total of users that have voted?

Average Age
I have a dating script running which uses MySQL to store the users and their information. Now that I have some users, I would like to create a PHP file to show some site statistics. Actually I would like to get the average age of the users that are stored in the database, for example something like

Average age: (At this point I would just like the average age, for example 27.33)

Is this possible in some way?

Average Age
I got this players table and I do want to list its average age...

SELECT AVG(YEAR(SUBDATE(CURDATE(), TO_DAYS(birth)))) FROM players

This works fine for me BUT, it doesn't on my server though it isn't 4.1.XX as it is on my computer at home...

7 Day Average
I don't know if this can be done but I'm looking to write a query that selects the LAST 7 records the and gives me an AVERAGE.
Basically I have a table which logs the number of hits each day, I want to select the last 7 and see what my average is. How do i write something like this?
there is is only 2 fields in the counter table called date and counter.

Returning A One Value Average
I'm using PHP and MySQL, and am trying to return an average (songle value).
My code isn't working, and I'm getting confused between the mysql_fetch, here's my code:

$average = "SELECT AVG(overall) as overall, AVG(gameplay) as gameplay,
AVG(graphics) as graphics, AVG(sound) as sound FROM VIDEO_GAME_DETAIL
WHERE name=" .$row['name'];
$result = mysql_query($average);
$rating = mysql_fetch_array($result);

// down in an HTML table
echo "<td>" .$rating['overall']. "</td>";

$row['name'] is the name of a PC Game from the VIDEO_GAME table. In the VIDEO_GAME_DETAIL table I have reviews for many games, with ratings. My goal here is to average the different ratings (overall, gameplay, sounds and graphics) for a one game, and stick them with the main page that lists the games (1 row per game).

I get this following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Users/Marconi/Grad/lleccia/www/ISYG250/proj4/reviews.php on line 54

line 54 is $rating = mysql_fetch_array($result);

Running Average
How can I get mysql to do a running average of a given field using dates
from another column so that the average for each element is for a given time
period rather than from the 5 rows next to the element?

Average From Two Tables
I've two tables storing the scores of test marks. The following show the selected scores from the two tables of a certain person with id=1 and level=5:

# table1
id level score
1 5 80
1 5 80
1 5 100

# table2
id level score
1 5 40
1 5 60
1 5 50
1 5 70

The sum from table1 is 260 and the sum from table2 is 220 for a total of 7 tests. The grand total is 480.

I would like to be able to obtain the average of 68.57 (480/7) for a particular person of a particular level. Would I be able to do that with a single SQL? I've tried a number of sql queries but the don't work.

Average Length
Basiically I have a table called ‘Members’ with the following attributes, (the m is short for member), Members (mno , mname, date_joined, date_left, maddress, mtel)
I need to produce a single SQL statement that will output the longest length of current membership, the shortest length of current membership and the average length of current membership.And the out put I get should be easily understood, not just a number if you what i mean.

How Many Is Too Many Queries On The Average Web Page?
I'm just curious... I'm trying to watch how many queries I run in any given page. But I find myself being too conservative sometimes, trying to think of better ways to do things... but then end up getting frustrated.

Just looking for some average feedback. In your opinion or experience, how many queries to the DB using PHP is too many queries? I know less is better... i'm just trying to get an idea.

Query Fetching Average
I have 2 tables in a database. One table consists of persons. Each person appears only once in the table.....

Average Of Grouped Fields?
SYSTEM MOUNT KB_USED KB_TOTAL

host_1 / 1000 30000
host_1 / 1500 30000
host_1 /apps 1092 234343
host_1 /apps 7266 234343
host_2 / 4598 553444
host_2 / 4545 553444
host_2 /opt 3435 435345
host_2 /opt 4534 435345

What i want is, something like this

SYSTEM MOUNT AVG(KB_USED/KB_TOTAL)*100

host_1 / 4.166
host_1 /apps 1.78
host_2 / 0.82
host_2 /opt 0.91

How do I do this? Basically I want them grouped by mount per system.

Average Over Results From Query
I would like to write a query in which i calculate the average over the result from an earlier query. I have a transaction header table and a separate transaction lines table. I am trying to count an amount per transaction by summing the lines table, grouped by transactionnumber and would like to calculate the average over all of these sums.

Average Time Query
I have come stuck with averaging a column with type of Time.

I know there is a function AVG() that I can use to filter the average of a column, however, this brings back an integer.

Is there any way to return the average in time format (IE 00:00:00)?

BACKGROUND INFO:

SELECT `estimatedTime`
FROM job_sheet;

Returns,

01:00:00
21:30:00
01:00:00

The data type is time, and the default is 00:00:00.

High Average Load (50%)
I have a Windows 2003 server with MySQL 5 installed (5.0.19). The server has a 3ghz processor and 2gb of memory.

The server has about 20 databases of the type MyISAM and there is almost no load trough queries. The mysqld-nt.exe process has an average load of 50% and goes up when a query comes in. Why is this? I have tried logging to see what MySQL is doing but I cannot find anything abnormal.

Average Time Difference
I have a table which includes two timestamps - creationTS and processedTS.

I want to run a query that tells me the average difference between these two timestamps on a minute by minute basis, i.e. one row for each minute of the day, containing the total messages created in that minute and average amount of time it took to process data created in that minute.

Is this possible in a query, or do I need to grab each entry and use PHP to work out the average processing time?