MYSQL :: PROGRAM TO UPDATE DATA TABLES

Can't Update A Field From VB Program
I've just setup MySQL on a Linux machine and I'm using Visual Basic in
Windows to connect to it (using MySQL ODBC 3.51 driver). Everything works
fine for browsing the database (used for stock control), but some parts of
the VB program refuse to update the database. It was using an Access
database (which worked ok) and I'm using the same SQL statement and table
structure.

Here's the SQL statement that VB runs:

SELECT Stock.GJGStockCode, Stock.PartName, Stock.Discontinued,
Stock.UnitsInStock, Stock.UnitsOnOrder, Stock.ReorderLevel,
Stock.ReorderQty, StockSuppliers.SupplierID,
StockSuppliers.OrderCode, StockSuppliers.PackQty,
StockSuppliers.Price, Stock.PartsToOrder
FROM Stock, StockSuppliers
WHERE Stock.CurrentSupplier = StockSuppliers.Position AND
Stock.GJGStockCode = StockSuppliers.GJGStockCode AND
((Stock.UnitsInStock + Stock.UnitsOnOrder <= Stock.ReorderLevel)
OR
(Stock.PartsToOrder > 0))
ORDER BY StockSuppliers.SupplierID, Stock.GJGStockCode

....which works ok for browsing the database. If I change the
Stock.PartsToOrder field in the VB program, when VB trys to update it I get
the error message "Insufficient key column information for updating or
refreshing". The Stock table has a primary key (GJGStockCode), and the
StockSuppliers table has a primary key (UniqueID) which is an auto_increment
bigint.

Does anybody know what I need to do to allow VB to update the database? Or
is this a VB question!?? - Although the same SQL statement works with an
Access database.

Data On Separate Drive From Program Files
I have a server that I want to run MySQL on. The server has a 6.4gb hard drive for the OS and applications and a raid array for data that is shared via samba.

What I want to do is install MySQL on the 6.4gb hard drive but have all the DATA saved to the raid array.

Update All Tables
Is it possible to update all tables in my DB on one query

Update Multiple Tables?
situation:
a user wants to change their username on my site and their username is littered accross many tables in the DB. If in every one of those tables i've got a static "user_id" column as well as the "username" column, what is the best way to update all of the different tables with the most efficiency?

Update - Mult Tables
Early experimentail stages with Databases. I have this little faq sys running on my dev server BUT just uiploaded to my host and what yer know it aint working..

two tables question and answers
two "Poted vars" $an and $id

I am trying to update the answer to a qu....

UPDATE answers,questions SET anstext = '$an' WHERE questions.ans_id = answers.id AND questions.id= '$id'
I read that multiple tables was only suppoerted on later vers on Mysql...OR is my syntax just wrong

How To Update Two Or More Tables Simultaneously
How can I update two or more tables simultaneously with single update query ?

Update 3 Tables At A Time
i have 3 tables namely--- artist, journalist and songs.
i try to update the flags in artist and journalist and try to update few fields from songs by firing foloowing qurries.

update songs set sname='lala', sname2='my baby' where artistid=6;
update artist set statusartist='new',flag1='updated' where artistid=6;
update journalist set statusjourn='new',flag1='updated' where journid=4;

when i fire the above querries individually they are excuted without any error.
when i try to run these querries through my JSP application only the songs data is updated and the rest 2 tables are not.

i tried to write 1 combined query including all 3 tables for this,but still it didnt work.
can u please tell me a solution for this.

Best Way To Update Multiple Tables At Once
I need to update a few tables at once. I have a form, were I fill in some information about a concert, which updates thez concert-table. Next to this, I need to update the bands-table, the statistics-table, and some others. What would be the safest, and fastest way to achieve this?

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.

UPDATE Linking Other Tables
I am trying to do a simple update function in mySql.
NOTE. I am using version 3.0.9.4-beta

I have 2 tables:
Items and Sales_details

I want to update a field called >Sales_value< in Sales_details table using value from Items called >Sell< times (*) >Sales_qty< from Sales_details table.

This is the codes I have been trying:

UPDATE Sales_details inner join Items on Sales_details.fk_Item_ID = Items.Item_ID
SET Sales_details.Sales_value = (Sales_details.Sales_qty * Items.Sell)

Error msg:[development_DB] ERROR 1064: You have an error in your SQL syntax near 'inner join Items on Sales_details.fk_Item_ID = Items.Item_ID
SET Sales_details.S' at line 1

and

UPDATE Sales_details
SET Sales_details.Sales_value = (select (Sales_details.Sales_qty * Items.Sell)
from Items inner join Sales_details on Items.Item_ID = Sales_details.fk_Item_ID)
where Sales_details.fk_Item_ID = (select Item_ID from Items)

Error msg:[development_DB] ERROR 1064: You have an error in your SQL syntax near 'select (Sales_details.Sales_qty * Items.Sell)
from Items inner join Sales_detai' at line 2

UPDATE With Related Tables?
I have a forum structure stored in a MySQL database and recent changes I made to a web site using it made it stop incrementing the "replycount" for threads (yup, pretty silly :-)) anyways, I am working on a stored procedure (or just an SQL statement) that I can call to recalculate the reply counts.

The two tables are structured like so:

mysql> describe thread;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(50) | YES | | NULL | |
| lastpost | datetime | YES | | NULL | |
| viewcount | int(11) | YES | | NULL | |
| replycount | int(11) | YES | | NULL | |
| author | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> describe post;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| tid | int(11) | YES | | NULL | |
| pid | int(11) | NO | PRI | NULL | auto_increment |
| authorid | int(11) | YES | | NULL | |
| body | text | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Is there a way to do an update query that will link the two tables, then group on the 'tid' field, then use the count result from the 'tid' field to inset into 'replycount'? Also, this is an example showing how the info is related, just in case I dont make it clear enough to understand:

SELECT thread.tid, thread.title, count(thread.tid)
FROM thread, post
WHERE thread.tid=post.tid
GROUP BY thread.tid

How Do I Automatically Update Tables?
I need some very basic information and ,being new to mysql, am not sure what keywords to use to search for an answer. Would someone please tell me either where I can find the info I seek or what they keywords would be?

I have created three tables and added foreign keys from tables 1 and 2 to table 3.

Example:
#1
person table
person_id pr key

#2
location table
loc_id pr key

#3
event table
event_id pr key
foreign keys: person_id & loc_id

Please note that these table examples have more columns than what is being shown.

When updating the event table, how do I get the foreign key colums to choose the correct person_id and loc_id and automatically update? So far, the mysql tutorial book does not address this in their examples so I must assume that the foreign key columns were updated manually.

Others have told me that this can be done using forms and subforms. If this is so, how?. And, can it not be done also in mysql using the command line?

If forms is part of the answer, I am familiar with them, having used them in my website. I am learning php and have created test forms to update my tables. So far, I can't get the tables to actually update although the php code states that I have connected to both the database and table but that is another issue. My point is that I have some knowledge of forms and php.

I just can not find any info telling me how the forms, subforms, php and mysql may work together to automatically update the tables.

How To Update Tables Automatic?
I have a table with all FOREIGN KEYS pointing to tables with PRIMARY KEYS. But when i add data to base table(Primary Key) it should auto update the dereived(Foreign key) table

Live Update Of Tables
I do have the following problem:
A local server running MySql includes the database A, another server in another region (connected to the internet) includes a mirror database of A. What I need is to live update the tables of the mirror database via a secure connection. Both networks have an internet connection.

Using Form To Update 2 Tables
how to change ths script below so it can update the same cells in another table aswell the current table. in other words updates two tables at the same time.

<?php
if ($submit) {

require("db.php");
$con = mysql_connect("$db_host", "$db_user", "$db_pass");
$db = @mysql_select_db("$db_name",$con);
$sql = "INSERT INTO support (prod_code,prod_name) VALUES ('$prod_code','$prod_name')";
$result = mysql_query($sql);
echo "Thank you! Information entered. <a href=admin.php>Click Here</a>
";

} else{

?>

<form method="post" action="<?php echo $PHP_SELF?>">
Product Code:<input type="Text" name="prod_code"><br>
Product Name:<input type="Text" name="prod_name"><br>
<input type="Submit" name="submit" value="Enter information">
</form>

<?php
}
?>

Update Data In A Row
I have a database with 1400 rows and we have a column called descript. But each content of the row in "descript" have different text. This text have words with a "?" sign.

How can rename each word that have a ? sign with another word?

for example:

Descript >> hello we are t?king a lot...

We need to replace the word "t?king" of each row with "taking"

Update Data
i get an excel report each month,
There are two columns (person`s name and Salary)
in this month report there was 99 persons.
my question is , if in next month excel report there are less persons...say 75, and also 23 persons raised their salary..
how can i updatae this changes in a mysql table that i have this mysql table has the exact same two columns and the exact same information i have in excel...
is there a way to do this?

UPDATE Multiple Tables Difficulties
I've been creating a stock update system that needs to be integrated with an existing database. To update the stock I need 2 tables updated in the same query (unless there is better way).

In one table, items_ordered, holds every single product that has been sold and has a field StockUpdated which holds a 1 or 0 depending on whether they have been stock counted already. This will stop future queries counting stock that has already been counted. i.e. the query will look and update each record that has a 0. So far the query is able to update this table successfully.....

Compare 2 Tables And Update One If A Match.
I have two tables "contact_tbl" and "address_tbl". I have a query that compares the email addresses between the two. If a match is found I need to update a specific field in "contact_tbl" with a value of 'yes'. My query is:

SELECT * FROM contact_tbl
INNER JOIN address_tbl
ON contact_tbl.email = address_tbl.email;

This works and I get a nice result set showing records where the email addresses match. I now need to update a field "status" in contact_tbl when there is a match. Have been tinkering with this for a while but not sure of the syntax and placement for the UPDATE code.

ResultSet Is From UPDATE. No Data.
I have a probleme executing a query using a PreparedStatement, it
results in a SQLException with the message "ResultSet is from UPDATE.
No Data.". Below I added the profiler log which indicates that the
statement-id changed between [PREPARE] and [QUERY]. Shouldn't it be
[PREPARE] and [EXECUTE] anyway?

INFO: Profiler Event: [PREPARE] at
org.jboss.resource.adapter.jdbc.BaseWrapperManaged Connection.prepareStatement(BaseWrapperManagedConn ection.java:360)
duration: 62 ms, connection-id: 0, statement-id: 43, resultset-id: -1,
message: SELECT name,value FROM Attribute WHERE foreignuuid=?
2006-09-18 10:47:30,176 INFO [STDOUT] (Timer-4) 18.09.2006 10:47:30 N/A
N/A

INFO: Profiler Event: [QUERY] at
org.jboss.resource.adapter.jdbc.WrappedPreparedSta tement.executeQuery(WrappedPreparedStatement.java: 296)
duration: 32 ms, connection-id: 0, statement-id: 44, resultset-id: 0,
message: SELECT name,value FROM Attribute WHERE
foreignuuid='c01b997f0a0dfd8e018a4aeffbfeb45f'

I'm using JDK 1.5.0_06, mysql-connector-java-3.1.13-bin.jar with JBoss
4.0.2 and mysql 4.1.10 on W2K. I'm using JNDI to get a DataSource and
retrieve a Connection object.

Can anybody think of reason why the statement-id changes? Is this
change the reason for the SQLException mentioned above?

Update Auto_increment Data
i need to re sort auto_increment value in mysql database.ex: there is 5 record - and i have to delete record no 3. while i'm using increment ID as row number.so it will display 1,2,4,5 . i need it to auto re-sort update on this fields.

Update Heavy Data
We have an os commerce application with a database of 7mb is working fine on our local server but when we are trying to update the data from local to online server via phpmyadmin we are unable to upload it there.
Is any other way or idea to upload the file to server?
"we can do it by root access of server but we dont have that acess in our hosting"

Can't Update Records When Using Linked Tables In Access
I have a linked table to MySQL 5.0 through MS Access 2003 that utilizes a timestamp field in order to prevent the #Deleted signature happening everytime I create a new record. However, when I attempt to update a previous record I receive the error message: "Reserved error (-7776)" and am subsequently prevented from updating any records. Please note, there are no errors when creating new records, only when I attempt to update an existing one. However, if I remove the timestamp field, updates occur without error. Has anyone else encountered this issue? How might I rectify it?

Update / Insert Data Using CSV File
I need to update a field in my database using a CSV file. The database already has the Product Number (pNUM) and I need to add Wholesale Price (pWholesalePrice).

The file I have is fomatted as follows:

AC1074,48
AC1076,60
AC1076A,60
AC3515,60
AC3521,60
P1207,78
AC2300RH,42
etc...

I would need to do some sort of UPDATE that would find the product number and insert the corresponding wholesale price. I can't imagine this would be difficult if I knew what I was doing.

INSERT Or UPDATE Depending On Row Data....
I have a form that submits to a php script which in turn sends the data to a table in a mysql database.

$sql = "INSERT INTO scores (scoreid, xuser, xscore)
VALUES ('', '$_POST[xuser]', ('$_POST[xscore]'))";
$result = @mysql_query($sql,$connection) or die(mysql_error());
?>

This is the main section of that specific script at the moment. As can be seen, there are three fields, scoreid, xuser and xscore.

What i want to do is Insert a new users score as it is currently done so, HOWEVER, if the user already exists in the database then instead of a new tuple being entered for them, how can i do it so that their old tuple is overwriten/updated instead?

Update MySQL Data From Excel
Ok I know how to get the data I want from mysql but I need to actually
do something with this data and then send it back to mySQL.

Company heads love the spreadsheet view so this is serious business.

I've done what I could with tables and css and javascript and whatnot
for a makeshift datagrid on a webpage but since I'm using PHP and not
asp.net I have no idea how to create a datagrid.

So if anyone knows how to update data in Excel that has been pulled
from mySQL it would be GREAT.

Update Data Without Having To Reinstall Indexes?
I use a large tab-text database by importing it into MySQL and setting up appropriate indexes. The database has just been updated. I'd like to drop the old data, and import the new data, without having to set up all my indexes all over again.

Is this possible, and if so, how is it done?

Import A Csv To Update Only Certain Rows Of Data?
I have an existing mysql table and a csv. I want to import the csv into the table, but it is at a different sort order. There are too many records to sort my csv by hand to match. There is a common 'cd_code' field that both have.

Is it possible to import a csv to the table so that each row of the csv is inserted in the correct row of the table, based on the common 'cd_code' field?

For example, if I sorted by cd_code asc, it would match the csv, but how to I import the csv into a particular sort?

MYSQL In A Program
How would I access a database from a program? All I need to connect in the program is the IP address or the URL, though I do not know how to get those from a database.

Also, I'm still having trouble with this:
http://forums.mysql.com/read.php?10,141978,141978#msg-141978

Mysqlimport Program
I have been using load data in file command until recently on a windows machine - works fine.

we are now using a Netware 6.5 box and are trying to use mysqlimport from the command line.

I am using:

mysqlimport --password=123456789 --fields-optionally-enclosed-by="" --fields-terminated-by="," 0608c sys:mysqlinstirling.txt

to import a CSV text file into the database 0608c.

the data looks like this

"2007-06-28 16:07:00",0,13.58,15.77,-13.35,-12.69,-13.36,-6.61,-3.821,......

when the program executes it seems to give the desired results except the timedate is all zeros...

Extraction Program
I am looking for a simple extraction/load program to load data daily from a
Progress 8.3B database via ODBC to a mysql 3.23.57 database. It does not
necessarily need to do any data-type transforms. It need to run on Windows
2000 and be able to run it as a service, or as a cron task if running under
Unix.

Setup Program
I'm writing a Java setup program for installing a JDBC app, which connects to a Mysql database.I want to create a script in order to provide final user setting privileges in that database and add it a table, without needing to write Mysql commands.
How can I do it ?

DB Modelling Program
Is there a database (ERM) modelling program for Mac OS X that can handle MySQL? Maybe a freeware one?

Php Email Program
Does anyone know of a php or even cold fusion based script out there for
accessing email that can be customized? Right now I use SquirrelMail
which is great, however, you can't really customize it to tie in with
your web site. Is there something out there that is very flexible that
I could make fit right into my site which is pretty reliable in terms of
web email?

LOAD DATA INFILE Or ON DUPLICATE KEY UPDATE
I want to use LOAD DATA INFILE to load a text file into my database because of the speed at which it can complete the import.

But I don't think I can use it so I want to check here first before I completely nix the idea of using it.

Is it possible, using LOAD DATA INFILE and possibly the REPLACE argument, to retain values from specific columns in the row that it's updating. It says in the manual that "You cannot refer to values from the current row and use them in the new row" but goes on to say something about using SET that I don't understand.

Is there another way to use LOAD DATA INFILE and to retain values from specific columns that aren't being updated by a field in the text file?

(i hope some of that makes a little sense?)

If it's impossible to use LOAD DATA INFILE, how can I speed up my ON DUPLICATE KEY UPDATE query. Right now, it just processes 1 row of the pipe delimited text file at a time:

UPDATE Fails On Somewhat Large Data Sets
I am running into a problem with an UPDATE statement. I am using MySQL 5.0 server, and the mysql command line client that came with it.

update tableA a, tableB b set a.value = b.value where a.key1 = 'foo' and a.id =
b.id and a.col2 = b.col2;

The tables use InnoDB as the engine. With small data sets, this works fine. However, with larger data sets, the UPDATE runs for a long time, then I get an error saying that the number of locks exceeds lock table size. In my case, the data sets in the two tables are < 5 million rows. I kept monitoring the InnoDB status, and see the number of log entries go up until finally rolling back.

I saw a bug on the mysql bugsite: http://bugs.mysql.com/bug.php?id=15667 which sounds like the problem I am facing - though my data sets are not as large as the bug states.

How do I overcome this issue? I was thinking about splitting the query into smaller ones, using LIMIT and doing some sort of ordering to guarantee same order of rows retrieved.

Any better ideas? Maybe configuring the InnoDB differently (I read that the lock table size cannot be changed, darn.)

Error On Update Or Insert Data But No Message
I have an application on asp and mysql 5.0, connecting with ODBC 3,51. When the application insert data or update data, MySql don't send error message, but the data don't update or insert. The transaction is ignored!!!!. Then, on my application I can't detect error. My code:

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_base_STRING
Command1.CommandText = "UPDATE EXAM SET MaAnt='"&MaAnt&"',Mois='"&Mois&"',Annee='"&Annee&"',[...]WHERE Id_Exam='"&id&"'"
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()

Have anybody an idea?

C- Program To Write To Myi , Myd File
is it possible to create a myi, myd file for mysql using a C program. I
am using Stata that is written in C API, and I want to write the data
out to MYSQL database. Using ODBC and loading the data in MYSQL from
STATA takes forever. Can anyone please tell me if I can create the myd
files by using the data in memory thru C programming.

Using MySQL To Input Into Another Program
I have been working on a workaround to see if I can figure out how to get DATA from MySQL into another program. The other program is built on MS SQL server. The problem is that it is so bloated that a remote and my computer at the plant cannot correspond back and forwards. It takes approximately 10 minutes to key an order in to the remote plant.

I was working on a much smaller database through MySQL and using OpenOffice.org as the front end. I just wanted to go a step further and see if there was a way to use a triggering mechanism of some sort to input the data from OOo/MySQL to the actual program the company uses.

Trigger To Run An External Program
Can I trigger an external program to run when a new record is added to
the mysql table?

The external program is in C and instead of scanning the table
continuously for new insertions, it will be better if an external
program could be triggered.

Good Program For Mysql
Do you now a good program for mysql admin like phpMyAdmin, than better!!

Linking C Program With Mysql API
I'm linking a simple C code with mysql. I compile the code with
this command:

linux> gcc -L/usr/lib/mysql -I/usr/include/mysql sqlTest.cpp -lmysqlclient -lsocket -lnsl

But it complains that "-lsocket" cannot be found. So, I removed "-lsocket" and
try again, then I got the messages like

/tmp/ccIelMdT.o(.eh_frame+0x11): undefined reference to `__gxx_personality_v0'
/usr/lib/mysql/libmysqlclient.a(my_compress.o)(.text+0x66): In function `my_uncompress':
/usr/src/packages/BUILD/mysql-5.0.27/libmysql/my_compress.c:85: undefined reference to `uncompress'
/usr/lib/mysql/libmysqlclient.a(my_compress.o)(.text+0x11c): Infunction `my_compress_alloc':
/usr/src/packages/BUILD/mysql-5.0.27/libmysql/my_compress.c:58: undefined reference to `compress'

My system is SUSE Linux 9.1, and I installed mysql by downloading the rpm files
from this site, and use "rpm -i mysql-xxxx.rpm" to install them. The libraries
are installed at /usr/lib/mysql, and include files are at /usr/include/mysql.
Can anybody tell me how I can configure my system to make it work?

Program MySQL With Apache
I don't even know what this is called, but I'm looking for a free tool that allows me to code MySQL and will work with Apache and PHP.

Program Refusing To DROP DB
I am getting this problem over phpmyadmin and also over a preprogrammed php script.
When there are more than 30 or 40 selected DBs the programs refuse to carry out any command when I click on the drop button.
They would just take as if there is no command issued at all.....

Anyone know how to make it delete more DBs at one time?
I looked at trhe my.cnf file but don't see any options to do this...
It however runs on small batches but I need it to drop a lot more databases at one time.

Truncate Tabel And Insert Data How To Update Indexes?
Once every day our financesystem updates data that I uses for my database. Some tables I truncate and reinsert data to get the latest changes in our customer and prospect database. Others I delete from a certain date to obtain best economical data and finally one database with 14M records I add records that has been added since the day before.

What I find is a very slow system and the funny part (but I think understandable) is that if I do not update for lets say a week (because I test stuff) the queries are fast and smooth.

That tells me that the problem is indexing.

So if I truncate a table, how to update indexes or can I do that at all?

The Optimize tabel doesn't help me here :(

I use MyISam and not InnoDB would that make any changes?

What about indexes are they needed (those I create)? or can MySQL handle that better?

1146 MySqlCC Error Cant Update Table Data
I am working with MYSQLCC (Control Center) and when I try and add data to a table using the run query it errors out with error 1146.

error 1146. table rossoptical.1 doesn't exist.

my table name is asphericallenses

It seems like there is a reference pointer problem!

This is my first time using the MYSQLCC and I dropped the table and recreated it with all of the data. I Still the same problem.

I have read a lot about other problems here and I think I have done everything suggested.

even when I run the select query I get the same problem, but I do see the columns of data, I just can not change the data.

Program To Optimise Mysql Queries
Basically im looking for a program that will take input (my query) and produce an output with a much nicer, more efficient query. I have seen sql programs that do this sort of thing, but nothing for mysql.
Just wonder if there is any programs that i have missed? Tryed Toad for mysql, but that wasnt what i wanted, i dont want a program to admin a mysql database, simply to produce a more efficient query.