MYSQL :: Updates And UDF Statements

Updates
if there's a query that could help me 2 update my table fields as follows.
It needs to 2 tables.

Withing table2 i needs to update the same field twice 2 different records e.g. set name='1', name='2' where id='1' and id='2'.
I've tried it like that but all it does is to update both records with 2.
What i wanted 2 to was to update "name=1 where id=1" then "name=2 where id=2".
Can anyone help me on this?

SQL Updates
This is so weird, I'm writing a little poll thing for my website and I've run into a problem..
$update = 'UPDATE mith_poll SET ans7N = 3 WHERE id = 1';
mysql_query($update);
When I vote on an answer it will go up +1 but when I pick a different answer after the last field has been updated the same field gets updated again, I then have to vote again to make the second choice go up + 1..
Am I making sense?
I'll try a bit harder for those who don't understand..
I select answer A
Answer A gets updated
I select answer B
Answer A gets updated
I select answer B,C,D,E and which ever I picked gets updated
I select answer F and the last answer gets updated

Updates Not Consistent
I got a simple application in C# where I have a text box and the userinsert the serial number then I will check if its status in thedatabase is 'y' then I am able to select it and set a new status as's'. The problem here if I do it 2 for different serial number oneafter another for ex. first I select 00123 and then 00124. First the00123 is set to 's' but then the moment I select 00124 the status of00123 gets back to 'y'. I am using the same set of code for both 00123and 00124. There is no place where I run a query which sets the statusto 'y' and I dont understand why the status of 00123 can become to 'y'when it shows in the first place it has succeffully updated to 's'.Below is my code. I have commented the places what I do exactly.

MySQL Updates
We have a new application that will be using MySQL i haven't used it before so have some time put aside to get the hang.

I downloaded mysql-essential-5.0.45-win32.msi and installed it i have the app up and running ok but i'd like to know how i find out what MySQL patches are available and if they need applying. Is there a live update style facility?

Automatic Updates
I'm not sure if this is possible or not, but is there a way to automatically insert data and delete data in a database at specific times during the day?

Is there a script or something out there that would allow me to do this?

Substrings In Updates
I have a column called full_name that contains name like 'amar ram patel'.
I want to update the same table by breaking the name in 3 different values using space as delimiter and add it to the respective columns viz. name, middle_name and surname.
I guess substring_index function can be used for the same.

Trigger Updates
If a certain table is updated I want all other tables which contain some of its attributes as a foreign key to be updated as it is updated.
However the tables that I want to trigger updates from is ISAM
and the other tables are INNODB.

Table Updates
There is a field in a MyISAM table that gets updated each time a PHP page is reloaded, providing an indicator of continued presence on a web site. There is also an app on the server that watches this field for 'logged in' users and logs them out if the timestamp doesn't update for several minutes. Mostly it works, but sometimes it seems that the value in the database table isn't being updated. I.e. the web page refreshes, but the server-side app continues to get the same timestamp.

I'm pretty new to this environment, so I didn't attempt to layer session logic (transactions) over the top of the basic 'update table' and 'select' commands.
Is there something I can put in the php file, or a configuration option on the database or server that would insure the data is committed to disk? (and, since the docs say that happens when the php script terminates..., is there something else I might not be seeing in this situation?)

Multiple Updates.
UPDATE site_settings SET
value = '$title' WHERE setting_name = 'site_name'
value = '$keywords' WHERE setting_name = 'keywords',
value = '$description' WHERE setting_name = 'description'
How can I perform this in ONE Query?

I Have Way Too Many Inserts/updates In My Forum
when someone posts a thread theres 2 count queries
2 fetch queries
3 insert queries
and 2 update queries

so my question is, is there a way to do a sort of join query for inserts and updates?

also, has anyone got some mysql musts?
i dont see a mysql tips thread
i think there should be one

Optimizing Multiple UPDATEs
Basically what I want to do comes down to this:

UPDATE players
SET ranking = 1
WHERE id = 45;

UPDATE players
SET ranking = 2
WHERE id = 30;

UPDATE players
SET ranking = 3
WHERE id = 8;

UPDATE players
SET ranking = 4
WHERE id = 97;

UPDATE players
SET ranking = 5
WHERE id = 12;
I do have a php array containing each player ID with its (new) ranking. However looping through all the players to update their rankings (as shown above) does not seem like a scalable solution to me.

Finding Out Table Updates Since...
I want to synchronize 2 sets of tables and need to be able to tell wich updates the offline set has had since the last time it sychronized the online table. Then i want to run these queries against the online table so their both in sync again.

Is there a MySQL way to do this or will i have to store the SQL queries ran agains the offline table manualy?

Updates Not Visible To Client
I have a Java application (app A) which "polls" a MySQL database table
for records with a certain column set to 0. It runs continuously,
utilising the same commection repeatedly.

The other day, I had a support call raised, whereby there were clearly
suitable records in the database, but my application was not selecting
them.

I wrote a standalone test app (app B) to read the same table and list
the records with the column=0. Sure enough, this app listed records as
requested.

So, app A retrieved zero records whilst app B retrieved many. Both
apps *are* using the same select statement.

I think it might be something to do with the rules governing when
updates become visible to clients.

The application that writes records to the database is not mine so I
don't know all the details. It's a php webapp that seems pretty simple
and just does plain inserts into the table with no attempt to do
anything non-default wrt locking afaik.

Updates To Multiple Tables
can i insert multiple records into multiple tables using MySQL:

statement.executeUpdate("INSERT INTO members, members2, members3, members4, members5 SET
La_Na='"+lana+"', Fi_Na='"+fina+"', NRIC='"+nric+"', CCode='"+ccode+"'");

is this possible?

Safe-updates From All Clients
This is version 5.0.24a on a Windows based machine.

If safe-updates is specified in the [mysql] section, if using the MySQL command line client, safe-updates does work! However, if using another client, or a web page for example to access the database, safe-updates does not work.

Is it possible to have safe-updates work in all clients, such as through connector/ODBC?

safe-updates in the [mysqld] section does not work (the server will not even start with it there).

So the question is: Is it possible to specify safe-updates somewhere so that it is global to every connection that accesses the database no matter what the source is for that connection?

User Updates On MySQL With PHP
I am trying to do is to have a login form in html or php that when a user enters a user name and password that they are taken to a page where their editable information in a mysql database is displayed for them to change.

Mass Updates To A Table
I've got a web-based form that will allow people to update a database
that is displayed as a table. Let's say that there are several columns
across titled Red, Blue, Green... and several rows titled 01, 02, 03...
In each cell, they can fill in some simple text data.

The only way that I can think of to update the database with this
information is using two imbedded loops in Perl:

pseudocode:
for($color=1;$color<=10;++$color) {
for($number=1;$number<=10;++$number) {
update table xyz set value=$value[$color][$number]
where color=$color and number=$number;

This would mean that it would have to execute 100 update statements.
Perhaps this is no big deal and I shouldn't worry about it, but since
I'm relatively new to sql, I'm guessing that there's probably a more
streamlined method to do this.

BTW - I've seen a lot of people asking questions that were vaguely
similar to mine in this group, and they've been told to go to the php
group, even though I didn't see any php-specific code in their
questions. I don't use php and don't have it installed on my server.

Multi-Table UPDATEs
on my old server I developed an application in MySQL 4+. Now I am trying to run it on a different server that only has MySQL 3.

Previously, I used these queries:

CREATE TEMPORARY TABLE x
SELECT *, 0 AS flag
FROM user
WHERE active = 1
ORDER BY id
LIMIT 0,25

UPDATE x, othertable
SET x.flag=1
WHERE x.id = othertable.userid AND othertable.type = 8

Is there any way to do this same sort of thing - update data in one table based on data in another table in MySQL 3?

Detect And Track Updates
I'm trying a develop a connector (or synchronizer) between MySQL and OpenLDAP(very similar to Metadirectory connector). The connector has to detect any updates made to the mysql database and should replicate that in OpenLDAP. The first part of it, is how to detect changes in MySQL? I thought of the option of using a Timestamp variable as an additional column in my DB table. But it won't work for 'delete' detection. Please let me know how to do that or point me to any source regarding that. Please reply soon as this is a part of my project.

Newbie: Formating Data In Updates
My POS products table is formatted totally different from that of the Online products table. No problem, I've got FileMaker Pro and it is really good at changing and formating data how I want it. I can output the product records for my website one way, the output the records for Froogle in another format.

The problem is that it takes and extremely long time with 70k+ records. My current scenario is outgrowing itself and I need to come up with a better solution. I need to find a quicker solution, possibly one I can automate down the road.

Any advice for resources? Or examples?

Unwanted Updates Follow A Select
I update a field after a very banal query (call it the WANTED update).

After that, this crazy thing happens: if I load a page of statistics to see the newly updated values after the mentioned update query has run, it results updates by 3 numbers rather than increased just by 1.
I am absolutely, psoitively sure the WANTED update increased only by 1: I printed the result all in the page, no weird addition ensued.

If I reload again the statistics (that is, I deliver one more select), it doesn't happen.

Morale, an update, followed by a select, updates (this is not wanted!) the selected fields of more than 2 or 3 digits. If another select follows on the same table, no unwanted updates occurs.

Best Way To Issue Hundreds Of Inserts/updates?
What is the best way to execute several (hundreds of) inserts and updates?
Rather than issuing tons of individual inserts and updates, can I send the
strings to a text file and then have mysql do them all??

IE :
query.txt

insert into table1 (col1,col2) values (1,2);
insert into table1 (col1,col2) values (9,4);
update table1 set col2=0 where col1=9;
insert into table1 (col1,col2) values (5,2);
insert into table1 (col1,col2) values (6,2);
insert into table1 (col1,col2) values (4,2);

Whats the best/fastest way to execute a few hundred of these?
Should I lock the table and then somehow use mysql.exe to run all the
commands in the txt file??

Updates Invisible - Windows Only (Linux OK)
I have one Java app writing records to a database (App A) and another,
a Java web application running under Tomcat, displaying the records.
Call this App B.

When I first log into App B and get a connection, all records in the
database at that point get displayed OK.

But after that, any new records inserted by App A do not get displayed
when I refresh (running the query again).

If I log out of App B and log in again, getting a new conection, I see
all the new records.

This behaviour only occurs if I'm running on Windows. If I run the
database on my Linux box with the same application code, new records
inserted by App B are immediately visible to App B with no need to
close the connection and open a new one.

Syncronizing Updates From File To Mysql
Is it possible to upload just the changes made to file since last time
it was imported/updated to the database.

For Eg:

1. File A has String "One"
2. This get uploaded to mysql table
3. File A is updated and now it has
"One"
"Two"
4. Only "Two" gets uploaded to mysql.

Audit Trail Of Updates And Deletes
In my application I need to keep track of all the changes, especially
update,

and delete changes each record. I need to keep track of who did
what changes. Is there any audit trail feature in MySQL 4.1? Or is
there a simple data schema, which can accomplish that.

ALTER TABLE And Logging Updates During Process
The documentation states that the ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other clients.

So that makes complete sense and is logical. But it then goes on to say,

"Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates."

I'm hoping somebody can shed some light on '...are stalled until the new table is ready...' I assume MySQL is storing these writes somewhere, either in a memory queue or perhaps on disk but I'm not sure. Actually Where it stores the information in this Gap?

Multiple Updates Per Row In A Multi-table Update
I'm wondering how exactly a multi-table update works. I'm using the following queries.
I expect t to contain (1, 2) and (2, 0), but it contains (1, 1) and (2, 0).
Why? .....

Forwarding Insert/Updates From Slave To Master
Is there a way within MySQL to forward all Inserts/Update requests from the
Slave to the Master, or much I rearchitect my application to carry two
connection; one connection to the Master for updates and the other
connection to the slave for Selects?

Updating Column1 Updates Also Defaultet Column2
I'm using 5.1.11beta and tried to update column1 of a row, which resulted also in an inherent update of column2 (TIMESTAMP(14) default current_timestamp).

To my understanding, an update of one column of a row, should not end up in updating also some other column(s). Even if i use the column2 as predicate in the where clause of my update-query, it ends up changed after the update.

Building A Stored Proc That Updates Using An If Test
I need some help building a sp with an if statement (really need to get a book but hoping someone can help me out right now)

I have a table with the following fields:
User varchar(25)
UserName varchar(50)
PUID varchar(25)
TS TimeStamp

Now what I want to do is make an SP that checks the Timestamp with a timestamp of a in paramater and if they are the same update the record with changes so I will have parameters for the other fields too and if the timestamp in the table is more recent than the parameter have a flag var returned by the sp. Problem is I have no idea how to do this in mysql can someone help me out.

Changed MyISAM To InnoDB - Getting Really Slow UPDATES
I've recently got all inspired and purchased "High Performance SQL" - an O'Reilly's book. Using a slow query log I'd already identified two tables that were causing problems. Both had an equal number of SELECT and UPDATE/DELETE statements being run against them.

This was leading to table-locking problems and after reading about the advantages (and disadvantages) of InnoDB tables versus MyISAM tables I thought that it would be good to convert both tables to InnoDB.

The conversion was carried out using:
ALTER TABLE `table_name` TYPE = INNODB

Scheduling Replication To Avoid Bottle-neck Updates
We have a circular master-slave setup where any one of the 2 servers
can become master at any time (by human decision). The two servers are
placed at geographically different sites. The servers contain à number

of databases which are all replicated both ways.

When we have full usage of one master ~500 inserts/updates per second,
the bandwidth between our sites becomes a significant bottle-neck. This

we can accept at database level not on server level, ie
- if database A on site B has a lag of 30min because of important
activity on database A on site A, it is acceptable.
- if database B on site B has a lag of 30min because of important
activity on database A on site A, it is not acceptable.

Is there a work-around? We never have updates concerning 2 databases in
the same query.

Creating multiple mysql servers at each site could be one, but that
means some 10-50 servers on every physical computer. What side-effects
does that create?

LOAD DATA INFILE, Updates All Other Fields With Nulls.
I am using the following command to update three fields ONLY in the destination table. The infile that I am using was created using:
..........................................................
select code, desc2 , qty_on_hand from plu_tmp
into outfile "c:mp.txt"

..........................................................
LOAD DATA INFILE "c:mp.txt"
REPLACE INTO TABLE `plu` (code, desc2 , qty_on_hand)

I expect to update only the three fields, without touching the other fields.
What happens is that the specified fields are updated, and all the rest of the fields are reset to nulls as they have not been specified.

Is there any way I can use the load inifile to insert / Update just the
specified fields.

Mysql User Profile Update Problem With Country Dropdown Menu Updates
I have trying to resolve this myself for a few days, but despite referencing two books and falling short on finding sample code on the web for this, I would wellcome your assistance to help me resolve this.

All I'm trying to do is to have an edit account form where the user is able to change their country_id field
in the user table, through a dropdown of country names that is stored in a country table with an (id, name, iso) fields. So far I have been able to do this:

1) register a new user and store their country_id field,
2) display the user's country name with all other user info on the "summary info" page,
3) and I have managed to display on the edit page in a single dropdown the users country 'selected' and all other countries.

The only thing that is not working now is when I try to change the country from the dropdown menu on the edit profile page and click update, nothing happens, the users old country selection remains. I have the value="country_id" for both options as you can see in the dropdown, but its not working. I suspect the variables are getting mixed up somehwere, or I'm not calling them in the right way, but I feel like my head is going to explode soon I have included below the code. I look forward to hearing your advice. T

Miro

<?php
if (isset($_POST['submit']) && $_POST['submit'] == "Update") {
$query_update = "UPDATE user SET " .
"first_name = '" . $_POST['first_name'] . "', " .
"last_name = '" . $_POST['last_name'] . "', " .
"nick_name = '" . $_POST['nick_name'] . "', " .
"alt_email = '" . $_POST['alt_email'] . "', " .
"mobile = '" . $_POST['mobile'] . "', " .
"country_id = '" . $_POST['country_id'] . "' " .
"WHERE username = '" . $_SESSION['user_logged'] . "' " .
"AND password = (PASSWORD('" .
$_SESSION['user_password'] . "'))";
$result_update = mysql_query($query_update)
or die(mysql_error());

$query = "SELECT * FROM user " .
"WHERE ............................

SQL Statements
Ive got a database with the following two tables:

Person(name,address,cityname)
and
City(cityname,population)

I want to returen all Persons that live in the city of Bristol. To do that what it be:

select * from Person where name = "Bristol"
?

What about about a statement what will count the number of Persons in Bristol? Would that be:

select count (*) from Person where cityname="Bristol"
?
Finally if i wanted to setup a statement which will return the population of Jim Smith's city, would that be:

select population from City where name="Jim Smith"
?

Thanks

Using IF Statements
This is the first time I've used an IF statement so I'm not sure how it works. I've worked with databases and php though for years. Here's what the tables look like:

CREATE TABLE `dd` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`dd` varchar(5) NOT NULL default '',
`fig` varchar(15) NOT NULL default '',
`direction` set('f','b') NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `rename` (
`id` int(11) NOT NULL auto_increment,
`sid` int(3) NOT NULL,
`uid` int(11) NOT NULL,
`newName` varchar(100) NOT NULL,
`creation` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
Basically what I'm doing here is I have a trampoline program that calculates difficulty and I'm allowing people to rename the skills so that they can have their own custom names (there's several different ways of expressing the names of different skills). There's going to be many users, though, so I can't simply just rename the skill, instead, I have a separate table (rename) so that individual skills can be renamed for each person. Now, however, I have a text box that the person can use to start typing the name of the skill and through AJAX a list of matching skills appears below the box. I need the box to search the newName in the rename table if has been renamed, but search the name field in the dd table if it hasn't. Here's what my query looks like so far; it doesn't work but I know I'm somewhat close but I don't know the correct syntax for this if statement.

SELECT *
FROM `dd`
LEFT JOIN `rename` ON
dd.id=sid AND uid=1
WHERE
IF rename.newName IS NOT NULL THEN
`newName` LIKE 'Triffus%'
ELSE
`name` LIKE 'Triffus%'
ORDER BY `name` ASC

Using IF Statements
I have the following code:

IF (SELECT id WHERE base_narrative_id = 1) THEN
UPDATE `user_narrative`
SET `new_message` = 'You have new message'
WHERE base_narrative_id = '1'
AND user_id != '1'
ELSE
INSERT INTO user_narrative(id, base_narrative, user_id, new_message)
VALUES('', '4', '2', 'new message')
END IF;

But it doesnt work. I think the problem is with my if statement. How should it look? All i want to do is find out if there is an entry for the specified base_narrative_id, and if there is update it, if there's not create one.

SQL Statements
I'm a t a loss and maybe it can't be done, but I thought I'd ask here befor giving up. I wan to evaluate the result of a query, then run another query based on the results. For instance, let's I have:

SELECT locality.Geom As point
WHERE locality.LocalityNr='1'
IF point='point'
THEN do something
ELSE do something else;

My searches on MySQL and IF haven't yielded me much with regards to this logic.

Can You Merge These Two SQL Statements?
The first one is to get the row numbers for my paging.
The second one is what is ran for the actual paging.
I assume you can't join the two because of the LIMIT and offset values.
Am i right in thinking that?
Thanks
Matt

$query = "SELECT COUNT(ct_vehicles.id) AS numrows FROM ct_vehicles WHERE (".$where.")".$minPriceDB.$maxPriceDB." ORDER BY ct_vehicles.price;";

$search = "SELECT ct_vehicles.* FROM ct_vehicles WHERE (".$where.")".$minPriceDB.$maxPriceDB." ORDER BY ct_vehicles.price LIMIT ".$offset.", ".$rowsPerPage.";";

Grant Statements...
i understand Grant statement is used to specify access limitation to certain users in what they can do to the tables/database ~
i have a table as shown in the statement below:-

create table admin(UserID int(4) zerofill not null auto_increment,
UserName varchar(15) not null,
Password varchar(15) not null
primary key(userId)
);

i can create a set of access privileges for one particular username ~ what if there are some new users being registered into this table??does it means that i have to type the grant statement for each and every new users again and again~ to specify the limit for each new users?

Multiple SQL Statements
I wanted to know how I can make multiple select statements returin into one single row. I tried, but something funny happens:

this works, I get one row with columns currentdate and total filled with data...

select curdate() as currentdate,
sum(amount) as total from bills where date="xx-xx-xxxx"

this DOES NOT work, I expected to get one row, with column 'deposits' and column 'total' filled

select sum(amount) from deposits where date="xx-xx-xxxx",
sum(amount) as total from bills where date="xx-xx-xxxx"

using mysql 4.1

Is this a standard SQL limitation or am I not aware of something?

Importing Sql Statements
I have a text file with about 90,000 insert statements (as below). Is there an easy way I can get these into my mysql database in one fell swoop? Can anyone tell me how and perhaps that the syntax is?

Select Statements
I am trying to find all fields with a distinct value. example:

SELECT * From $Monthn With('%s') and getting error messages.

Insert Statements
I'm using bugzilla on Solaris 9. I'm trying to determine why bugzilla displays some bugs, but not others. Below I have two mysql insert statements. The first displays properly. The 2nd does not.

I wonder if someone can/would tell me why? [ Yes, I know this is a mysql forum. I would appreciate it you can tell me how these two statements differ from that degree. ]

INSERT INTO bugs VALUES (2,1,'','normal','CLOSED','2004-09-24 21:47:49',20040925181616,'TEst 2','Windows XP','P2',2,'PC',1,'1.0',2,'INVALID','--
-',0,'',0,'','2004-09-25 18:16:17',1,1,1,0.00,0.00,NULL);

INSERT INTO bugs VALUES (15,1,'','normal','NEW','2004-10-06 09:54:25',20041024210155,'Logging configuration doesn't work correctly','Windows XP
','P2',2,'PC',1,'1.0',21,'','---',0,'',0,'','2004-10-24 21:01:56',1,1,1,0.00,0.00,NULL);

Also, how would I reverse/delete either of these statements?