MYSQL :: Updating Exisiting Rows From A File

Updating A File
FIXED PROBLEM: I missed a comma in the UPDATE statement. I am trying to make simple site that allows news to be posted with one file attachment (such as a picture).

I have the initial inserting done just fine. When I am trying to update the record (for if the news article needs to be edited), I cannot figure out how to replace the existing file attachment.

Here is the code I am using that will not UPDATE the file but will update all the other fields. Can someone please help me correct this? Code:

Updating Rows
I have a table say like this:
t1

v1 AUTO_INCREMENT
v2 char(40)
v3 int (its empty, need this in here!)
and say I have a CSV file with the values that I want to add, not in the same order as the values in the table, but the first column contains the index, v1.
1, "1234", 5
So is it possible to import like this? or would it be better to dump the whole table into a CSV file, add the data I need to and reload it in?On that subject, if you set a value of the auto_increment column when using LOAD DATA INFILE, will it set it providing that the value does not exist twice as requsted? or will it ignore it and auto_increment one up? because i need all the numbers to remain the same as they are linked into other tables from this index value!

Updating Rows
I have a table like this:

id name
-- -----
1 red
2 green
1 blue
3 yellow
4 black

I want to update the id's to 5 for names = red,green,black

How can i achieve this in one query?

Updating Rows
I am trying to update a table with content from a different table.

specifically, I am trying to add city and province info to rows which already have name and email info.

if I run this query

INSERT IGNORE INTO `jos_acajoom_subscribers` ( `city`, `province`)
(SELECT city,province FROM jos_fabrik_formdata_1 )

it adds new rows for contacts not already in the table, BUT it does not update existing rows.

Adding A Table To All Exisiting Databases
I've been using mysql for 3-4 years now, but am a bit rusty. I'm trying to add to an app that I wrote for my personal business a few years ago.

What I want to do is add a new blank (or populated) identical table to all the databases (except perhaps to the databases 'mysql' and 'test' which come with the system), using the CREATE TABLE command. I already have this command stored as a file (such as addtablescript.sql).

For example I could do:
mysql -u root -p dbname < /pathto/addtablescript.sql

But this would add the new blank table to just the database "dbname".
I want to add it to all of the databases without having to do this manually for each one, so I can write a small batch script for my wife, etc., and she won't have to do the hard work, also others that use the program.

Is there some way I can globally add the table easily to all the databases?

Since I also use php, perhaps if the above is not possible, I could run a loop in php to do this with mysql_fetch_object and then an exec command for each database,
but I'm trying to find the easiest way.

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 A Text File Using Php
well I have this text file "lingo.txt" that has a list of abbreviations which are used in a game I play. to make thing interactive i was hoping that visiters would be able to add there own abbreviation and the meaning of it to the "lingo.txt" file and then other people can download it

I know you can easily do this with a MySQL database (which I do have)but I wont them to be able to downlaod the list of words

I have found a way to update the file using the fwrite and fopen comands but thats deletes the last input where as i want them all save to a long list.

Updating Multiple Rows
The following UPDATE query works fine if run directly into phpMyAdmin (I take the $sql output of the script with the data in it and paste it into phpMyAdmin).

But it doesn't update my records if run from the PHP script. I can't seem to figure out where the bug is: Code:

Updating Multiple Rows
I need to update multipule rows a once, using PHP. Here's what I mean.

I have a mysql query out put the data as a form like this:

Item 1
<input type="hidden" name="id" value="1">
<input type="text" name="order" value="">

Item 2
<input type="hidden" name="id" value="3">
<input type="text" name="order" value="">

Item 3
<input type="hidden" name="id" value="4">
<input type="text" name="order" value="">
<input type="submit">

Order need to be updated. I could setup to do a loop with multiple queries.

Updating Rows While Fetching?
I'm trying to be able to do somthing here, my problem is figuring out a way to check through every row in a table, and write the timestamp date value that results in my set of if statements in a specific field of the row that I'm checking. Here's the code that I've written so far, and it's not working... Any suggestions?

<?php
$db->query("SELECT * FROM gtraining WHERE start_date<NOW() AND end_date>NOW()");
while($db->fetch()) {
$thisid = $db->row['id'];
if ($db->row['date1'] > date('YmHis')) {
$nextdate = $db->row['date1'];
} else if ($db->row['date2'] > date('YmHis')) {
$nextdate = $db->row['date2'];
} else if ($db->row['date3'] > date('YmHis')) {
$nextdate = $db->row['date3'];
} else if ($db->row['date4'] > date('YmHis')) {
$nextdate = $db->row['date4'];
}
$query = "UPDATE gtraining SET next_date='$nextdate' WHERE id=$thisid";
}
?>

Updating Rows Problems
im doing a program in visual basic 6 with mysql as database, in some part of my program i edit some fields but when im try to update an error shows, it says:

"Runtime error: -2147467259(80004005) Key column information wrong, Too many lines affected in update"
i dont know why, when filter i only have one row, i see it with the recordcount command, here its the code:

'Conn its a conection that i made
rsPartidasOC.Open "SELECT entregado, restante FROM mifpartidasoc" & _
"WHERE apunta = " & txtNOC.Text & " AND codigo = '" & strCodigo & "'",
Conn, adOpenDynamic, adLockOptimistic
ntmp = rsPartidasOC.RecordCount ' here recordcount its 1, only one row
rsPartidasOC![entregado] = ValLlegadoTemp
rsPartidasOC![Restante] = ValRestanteTemp
rsPartidasOC.Update 'the error comes here
rsPartidasOC.Close

**VALUES**
txtNOC.Text = "2"
strCodigo = "ART1"
table mifpartidasoc:
id apunta codigo llegado restante
19 1 ART1 100 1000
20 1 ART2 10 900
21 2 ART1 200 1500
22 2 ART2 150 20

Updating Multiple Rows
I am running the following UPDATE statement

UPDATE history
set t_own = t_user * 0.75
where user_cur = 7
and lg = 'Local'

t_own and t_user are defined as DECIMAL 12,2

650 rows should be updated, but i get

error 1136 Column count doesn't match value count at row 1.

Updating Multiple Rows.
I went to MySQL home page and found one comment that kind of tried to explain it but it was written in such a manner that I could make no sense of it at all.
I am trying to find a way to update a row with 12 entries in a table that has 12 teams (teamid) and 12 ranks (r1,r2,r3...r12) the data to update the teamid comes from a form and they would all be updated (unless everything remains the same) with the use of the said form.
I am not certain how to make this work (call the table 'team')

PHP Code:

UPDATE team SET teamid='team1', teamid='team2'...teamid='team12' WHERE?

Updating Information In Keys With A CSV File
I'm looking for a way to make a autoupdate program that updates the tables inside my MySQL server. Does anyone know how I should make a *.exe like that??

I do no have much experiance with programming. But maybe allready has some script like that?

Updating A Text Field From A File
Is there anyway to update a text field for a given row from a file?
In other words I'd like to replace teh contents of a text field with the contents of a file.

Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers.

OK, here is the table

table test
------------------------
| test_id | test_order |
------------------------
| 1 | 1 |
------------------------
| 2 | 2 |
------------------------
| 3 | 3 |
------------------------
I'm trying to change the orders in one query, but not sure how to do that.

phpMyAdmin shows me the code like this

Quote:

$sql =
'UPDATE `test` SET `test_order` = ƈ' WHERE `test_id` = 1;'
'UPDATE `test` SET `test_order` = Ɖ' WHERE `test_id` = 2;'
'UPDATE `test` SET `test_order` = Ƈ' WHERE `test_id` = 3;'
. ' '

I'v tried that but got a syntax error.

MySQL version is 4.0.26, can anyone help please?

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

Help With Updating Duplicate Rows In Mysql Query.
Quote:

SELECT id, count(*) AS numlist FROM products GROUP BY category, name, brands HAVING numlist > 1 ORDER BY id ASC

What it does is find the duplicates. I also have a column called "app" which has a default set to "1". So now what I want to do is that when duplicates are found, I want the first duplicate row in each group to stay as a "1" and the others in the same group to be updated to a "2". I need this done for every group.

How can I do this. I have looked high and low accrossed the web, but can't seem to find any solution.

I have managed to find out how to group them and count the number of listings in each group, but I can't seem to figure out how to do the rest.

Also, if possible, I would like it to be done with one query.

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.

Saving Rows Into A File
I did search the forum but I found nothing.
Is there a SQL function that selects specific rows from a table and saves them into a file as SQL statements, which can then be executed later?
I basically want to save sme rows as a set of INSERT statements incase they get deleted and need to be restored.
This is intended as a tool for the client-tier so I would like the file to be created on their machine - not on the host server.

How To Discard Rows Of A Text File Which Does Not Satisfies Foreign Key Constraint And Continue With The Rest
I am loading data to a child table from a text file. the text files
also contains data not referenced by parent key. while loading the data
if one row fails to satisfies the constraint everything is getting
rollback..

plz suggest me something.. which will help me to discard the
unsatisfied rows and continue with the rest..

My Query:
LOAD DATA CONCURRENT INFILE
'/remote/srm172/saranya/SOURCECOV/common_shell/output/data/common_shell_exec.dat'
INTO TABLE CS_COMMON_SHELL_EXEC_REG FIELDS TERMINATED BY '#'
(reg_id,func_address);

Error Msg:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`sourcecov/CS_COMMON_SHELL_EXEC_REG`, CONSTRAINT
`CS_COMMON_SHELL_EXEC_REG_ibfk_2` FOREIGN KEY (`func_address`)
REFERENCES `CS_COMMON_SHELL_EXEC` (`func_address`))

How Do I Upload A File To MySQL Database And View That File
I do not know how to upload a file to MySQL database and view the posting file on the webpage. I followed the code on-line that show me how to upload, but when I click "browse" to open my file in local drive and click "upload". Nothing happen ! How can I view it on the web page to make sure it was saved in data base.

Log-error File 'not Found' But This Same File Being Written To?!
I recently put up a message board and I get around 150+ hits a day so far. I've recently had some people sign up with my board but unfortunately, no one seems to be posting (they're just lurking around). I'm currently doing some promotions in the hope that others will post (like giving away a Lord of the Rings poster to all users who has 20 posts or more).

Of course, my board has only been up for less than a month (3 weeks to be exact) so I know I need to be patient but I would appreciate any advice you can give me on how to turn lurkers into posters.

Searching For Rows That Depend On Other Rows In Some Fashion...
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.

I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.

For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...

Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

Please use my email for further conversion since I'm not a frequent
usenet reader.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

Updating From 4.0 To 4.1
My web host will update the server to MySQL 4.1 this week.
What's the difference? Should I look for something special in my code that might not work after that?
They will probably update to ver 5 later as well. But not yet.

Updating
If I have a bunch of cells that say: ,4,

and i want to update them with another number (5, and 6, -> $var) so that I would get results like ,4,5, and ,4,6, how would I go about updating that?

I notice, if I defined $var, and just use a normal update command, it just replaces it, but I want to keep the 4 in there (not necessarily always 4, but this is an example, as that too is a different number each time).

I tried using variables as well, but if I did anything within the while method, I couldn't bring it to the query. Any suggestions?

Updating A Row ....
I have an application for employee punch in/out. My table structure is, ID,Date,Employee Name,In-time,Out-time. Now when on the first punch in of the day, it will add a new record for that employee for that date only in in-time field. Next time, when same employee punches out, it should update the same existing record and put time value in out-time field. Next time when again, same employee tries to punch in, a new record gets added with value in only in-time field and so on..

Now, a totally weird thing happens. Whenever employee tries to punch out (so there is a value in in-time field), it updates that record as well as it adds a new record with same out-time value in in-time field. So in nutshell, it updates the existing record and adds a new record. I checked my script 50 times and put debugging code, but i don't find any errors.

Updating In SQL
UPDATE phpbb_auth_access SET auth_cal = auth_sticky;
UPDATE phpbb_forums SET auth_cal = auth_sticky;

How do I do this in MySQL?

Updating
Is it possible to do an update with load data infile?

We need to load a load of csv files into our database but the tables have additional fields over the ones in the csv files that we need to preserve as they contain the margins and profits for each item.

Updating
like...is it supposed to make only sense if you update the content of your database NOT via a standard rdbms ?and second...do i have to worry about what i ask mysql cause it is so slow?like...i have a query of 17 lines...does it matter at all?
and better ask 2 small queries or 1 large?

Can Updating
###### I am trying to update data in a vehicle_fuel column where the conditions match up to the costpergallon column. I need to know if I am doing this right and if this is possible, because at the moment it is not working for me.

sql = "UPDATE vehicle_fuel,costpergallon
SET vehicle_fuel.fuel_value=costpergallon.costper
WHERE vehicle_fuel.fuel_value='0'
AND vehicle_fuel.fuel_type_pumped=costpergallon.cost_type";

#### There should be about four different match ups with four different data values and I need it to loop through the entire table and update the data in the different rows.

Updating A Single Value In A Row
So i have a table with the following columns:

id
fname
lname
occupation
photo (holds the path to the photo)

and at some point I want to delete the path of the photo (i need it empty and not null)

UPDATE db.table SET table.photo=' ' WHERE id=$id

but this will delete ALL of my values along with the photo path except id!?

Updating To Mysql 5.1
trying to update to mysql 5.1 on mac os x 10.4.3 without much success. I've reinstalled mysql 5.1 and now in terminal when I try to start it with:

sudo ./bin/mysqld_safe & /usr/local/mysql/bin/mysql test

I get this error message 2002, server not running (http://dev.mysql.com/doc/refman/5.1/...-server.html):

[1] 1342
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
127:/usr/local/mysql garrett$ 070913 11:56:08 mysqld_safe Logging to '/usr/local/mysql/data/127.0.0.1.err'.
./bin/mysqld_safe: line 366: [: -eq: unary operator expected
070913 11:56:08 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
070913 11:56:11 mysqld_safe mysqld from pid file /usr/local/mysql/data/127.0.0.1.pid ended

so I have checked the error report as it says to do here (http://dev.mysql.com/doc/refman/5.1/...-server.html):

070913 11:58:17 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
/usr/local/mysql/bin/mysqld: Unknown error 1146
070913 11:58:17 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
070913 11:58:17 InnoDB: Started; log sequence number 0 46409
070913 11:58:17 [ERROR] Aborting

070913 11:58:17 InnoDB: Starting shutdown...
070913 11:58:19 InnoDB: Shutdown completed; log sequence number 0 46409
070913 11:58:19 [Note]
070913 11:58:19 mysqld_safe mysqld from pid file /usr/local/mysql/data/127.0.0.1.pid ended

what is the mysql_upgrade? where is it and how do I run it? any help much appreciated. This (http://search.dbanotes.net/doc/mysql...#mysql-upgrade) is a little enlightening but apparently I need to have the server running to execute something that is stopping the server from running - argggg!!!

Updating Most Recent/last Row
This is for a directory and the table Dir holds the selected plan of each member. The table contains a history of the plans (inserted on each selection) so what I need to do is update the last row of Dir.DirID.

Every time 'Posting' value is echoed on the page with query 'Listings' (two seperate tables from Dir - one for runtime and one for the actual postings (a foreign/primary key) - then Dir.Dview is incremented as a basic hit counter with the query Listings higher on the page.

What is the most reliable way of updating the last row of Dir.DView where DirID is primary?

note: query 'Listings' uses SELECT max But experimenting with this before, I hessitate to use SELECT max for an update.

<?php echo ($row_Listings['Posting']);
$query_updateSQL = sprintf
("UPDATE Dir SET Dir.DView = (Dir.DView+1) WHERE DirID = %s",
$colname_Listings);
$result = mysql_query($query_updateSQL);
?>

Updating Whole Database
I need to search my whole database and update a word to add â„¢ to it.

Updating A Specific Row
I have a question about updating a row in a table. This table keeps a record of when a user logs in/out with username, time of login/logout and ip-adress. When a user logs in, the logouttime is of course left blank. When that user logs out I need to update that row with the logout time. I know I need to use "UPDATE log SET timeLoggedOut = '$timeLoggedOut'"

I have a session variable with the identity of the user currently logged in. But how do I update only the last row in the table containing the username of the user logging out? If I only use WHERE username='$userloggedin' it will update every row containing that username.....

EDIT: I guess I could use $sql = "SELECT MAX(loginID) from log WHERE username = '$userloggedin'" to identify the row I need to change, right? But how do I return that information from the db so that I can put it in a php variable?

Updating Row Changes Timestamp.
I have a flagging system on posts... if someone flags a post it adds 1 to the "flagged" field. My problem is, whenever someone does this, it changes the timestamp so the post they flagged goes to the top of the list because they are ordered by postdate DESC.

Reading & Updating
I have a table where I have all my cars in. They have a brand_id and a model_id. I would like to read this cars table, see how many cars I have from every brand, and every model, and update my other tables, being brands and models.

Cars:
car_id, brand_id, model_id

Brands
brand_id, nr_cars

Models:
model_id, nr_cars

I can read every line seperately, and then within that loop do a UPDATE, but I guess there is a 'good' way to do this.

Updating Database
I'm fairly new to mySQL and php and seeking some input.

I created a database to hold a member directory. It is a single table.
The client uses some contact management software we're they keep track of their members. Therefore, the client does not want to enter the information through a form on the website. Instead the client wants to export a comma delimited file from the program and then use that exported file to update the database.

What would be the best approach to do this???

MySQL's LOAD DATA works fine as long as the original data does not change. If a member is removed or a primary key is changed, new rows are added but the old ones (that should of been updated) remain.

I figured whenever the client goes to update the db, a script deletes all data from the db, then imports the text file. This doesn't seem like a very efficient approach to me though.

Maybe importing the data into a temporary table and then comparing it to the actual table would work, though I am not sure how to go about doing this.

Updating Phpmyadmin.
How do I add this info to my database using phpmyadmin?

I have no idea as to how phpmyadmin works, so I will appreciate basic step by step guides to do this.... :

I need to add:

ALTER TABLE phpbb_themes ADD fontcolor4 VARCHAR (6) ;
ALTER TABLE phpbb_themes_name ADD fontcolor4_name CHAR (50) ;

Updating Fields
I'm trying to update values in one column. Some values contain numbers and some are blank. I need to update both but I'd like to do it one query instead of two but I can't figure out how. Example:

tableA has 2 columns, item and itemNum. Lets say there's 10 records and 5 values for itemNum are blank, the rest have a number. I need to update itemNum's all blank values to one number and update the initial values as well, but in one query.

In other words, I know how to make the query's one by one but I'd like them both in one query.

Example single query's:
UPDATE tableA SET itemNum = '10' + item WHERE item = 'cd';
UPDATE tableA SET itemNum = '20' WHERE itemNum = ' ';

btw, the blank values are not null, they just have a blank string.

Updating Tables
I have a database called "returnsMySQL". Inside there are two tables called "originaldate" and tempdate".

The "originaldate" has dates but it is not a date type field. 3 Years ago the field started saving dates under the short date format.

Example:

9/2/01

In order to get correct searches, I need this field to be updated to the 4 digit year:
Example
9/2/2001

I have tried using Active Server Pages, querying the database and trying to format the results using "FormatDateTime" the get the correct date but I have not had any luck taking the formatted date and saving it to the field "tempdate".

There are about 15,000 records in the database.

What would be my best option to accomplish this?

Updating Records
let us say for example i have a database called sales that has a table called sales.

sales has 4 columns: names, yr1999, yr2000, yr2001

names
-------
bob
carla
cynthia

yr1999
-------
3000
1200
8000

yr2000
-------
4000
6700
4500

yr2001
------
3400
3200
1200

what I want to do is update yr1999, yr2000, yr2001 by 10 percent so that when i do SELECT * from sales.sales I will be able to see all records updated to their new values.