PHP Update

Complex Query - UPDATE Within UPDATE?
Edit: Before anyone leaves this thread, don't be put off by the regular expressions! They are not the problem, so please stay and read.

OK, this query has got my head spinning. I am basically creating a query that goes through each product in a table to update the stock for that particular item with that particular size (i.e. I am talking about shoes - different models and each model has different sizes (uk kids 12 -> uk 11).

With each shoe it does (or is meant to do) the following:
1. The PHP script that runs the query is looping through every size outside of the query
2. So for each of these sizes it checks to see whether the product it is currently on matches the size it is on
3. When it finds the size it is on, it then deducts the correct number of units from the stock table
4. The final WHERE clause makes sure this subquery inside the UPDATE only happens when the StockUpdated field of the Product table equals 0 (in other words, the stock hasn't been counted before)

Basically what I need to do, is first to make sure what I currently have got does the above correctly but also I need the query to UPDATE the StockUpdated field to 1 only when it has been updated successfully. How could I do this? Unfortunately I cannot just add an extra update entry to the end of the query as this would update the StockUpdated field regardless of whether it has been properly counted or not.

Here is the query I have so far (with a little simple PHP around it doing the loop):


PHP

$shoesizes = array(1 => 'ukk12','ukk13','uk1','uk2','uk3','uk4','uk5','uk6','uk7','uk8','uk9','uk10','uk11');
    $numshoesizes = count($shoesizes);
    
    for($i = 1; $i < $numshoesizes; $i++) {
        $stockupdate = "
        UPDATE heelys_stock,items_ordered SET heelys_stock.size_".$shoesizes[$i]." =
            (SELECT
                CASE
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]12( )?(' -- if UK Kids 12
                THEN heelys_stock.size_ukk12 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]13( )?(' -- if UK Kids 13
                THEN heelys_stock.size_ukk13 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]1( )?(' -- if UK 1
                THEN heelys_stock.size_uk1 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]2( )?(' -- if UK 2
                THEN heelys_stock.size_uk2 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]3( )?(' -- if UK 3
                THEN heelys_stock.size_uk3 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]4( )?(' -- if UK 4
                THEN heelys_stock.size_uk4 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]5( )?(' -- if UK 5
                THEN heelys_stock.size_uk5 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]6( )?(' -- if UK 6
                THEN heelys_stock.size_uk6 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]7( )?(' -- if UK 7
                THEN heelys_stock.size_uk7 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]8( )?(' -- if UK 8
                THEN heelys_stock.size_uk8 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]9( )?(' -- if UK 9
                THEN heelys_stock.size_uk9 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]10( )?(' -- if UK 10
                THEN heelys_stock.size_uk10 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]11( )?(' -- if UK 11
                THEN heelys_stock.size_uk11 - (items_ordered.Amount/items_ordered.Price)
            FROM items_ordered WHERE items_ordered.StockUpdated = 0)
                
            WHERE (heelys_stock.id = (SELECT heelys_stock.id FROM heelys_stock,heelys_shoe WHERE SUBSTRING_INDEX(items_ordered.Product,',',1) = heelys_shoe.full_shoe_name))
        , items_ordered.StockUpdated = 1" // at the moment this last update of the items_ordered table happens to every record!!! even if the other part of query fails

        
        // update stock for size $i
        mysql_query($stockupdate);
    }

Hope someone can see how I can do this? I've been working on this query for 2 or 3 hours now and I've been making reasonable progress but now I am really stumped.

---

Update Replication, Force Update
Recently an error in the db on my master caused the slave to fail. I noticed this after a few days when I looked at the status and it said "Slave_SQL_Running: No". After looking further I saw what the error was on the master.

What is the best method of re-synching the databases?

Is there a command to force a re-replication or synch of the dbs? Would you delete the slave's db and update over? In this case, is there a command to pull the data down from the master?

---

Unable To Update - Have Update Priveledge
I've been granded update priveledge to tables in a few databases on our server:

Select_priv ,Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, and Lock_tables_priv are all set to 'Y' for a selected database.

I can connnect and read the data and data definitions just fine.  I cannot insert, update, delete, create, or alter any table in this database.

Our admin worked for a couple of hours yesterday trying to resolve the issue, but no joy.  I've googled for this problem and the most relavent posts I've found were problems related with users not being able to connect. I can connect and I can read, I just can't do any of the other priviledges.

---

ON DUPLICATE KEY UPDATE - Don't Have A Key But Want To Update
This may seem like more of a PHP question but I will come onto the MySQL part in a minute.

Basically I have a PHP script that lets users upload 2 different CSV files to different tables. The problem I have is that sometimes these CSV files are updated and then need to be reimported into the database through the script. I have made this script totally generic so that when you change the table that is being entered to the rest of the script will alter itself to import the correct CSV to that table. But a problem arises when I have a CSV file that contains each product sold and the shopper who bought the product (so the shopperID may appear in more than 1 row) - however this file does not have a unique id for each row meaning that when I use an INSERT INTO with a ON DUPLICATE KEY UPDATE... it will just insert more rows instead of updating rows because there is now key in the table.

The format of the table is like this (with some example data to show you more clearly):

+-----------+-------------+---------+------+--------+------+
| ShopperID | Product Code | Product | Price | Amount | Units |
+-----------+-------------+---------+------+--------+------+
546733 1315 prod 1 64.00 64.00 0
546733 1316 prod 2 43.99 43.99 0

OK just ignore the last 2 fields - that is a problem with the old system there was which does not add the units up right.

Anyway basically I might have the same shopper ordering 2 things which will come up on different rows - I have 3 things I cannot do:

1. I can't make the ShopperID field the PRIMARY KEY because I have many times where there are 2 rows with the same shopper
2. And I cannot just do an UPDATE when it gets to a duplicate entry because it will overwrite every time it gets to a shopper that already exists.
3. I don't want to add a unique primary key to the CSV as this will mean that the system I am creating loses its ability of being automatic because the person (an admin) that uploads the file will have to make sure that it has that extra field which is auto incrementing.

Does anyone have any ideas on how I can get round this?

Here is my PHP function which contains the query:
PHP

// this function for insert data to csv
function makeINSERTS($text, $table, $tablefields, $correctcsv){
    $insert = array(); //make array for hold data insert
    $i = 0;
    $success = true;
    
    while(list($key, $val) = each($text)){
        // Insert the data
        $insert[$i] = "INSERT INTO ".$table."  VALUES('";
        $insert[$i] .= implode("','", $val);
        $insert[$i] .= "') ON DUPLICATE KEY UPDATE ";
        foreach($tablefields as $k=>$field){
            $insert[$i] .= "`$field`='{$val[$k]}',";
        }
        $insert[$i] = substr($insert[$i],0,strlen($insert[$i])-1);
        // echo $insert[$i].'<p></p>'
        $result = mysql_query($insert[$i]);
        if(!$result) {
            echo 'FAILURE to insert/update any or all of the database form uploaded CSV!<br />'
            $success = false;
            die('Query failed: ' . mysql_error().'<br />');
        }
        $i++;
    }
    if($success == true && $correctcsv == true){
        echo 'SUCCESS in uploading the CSV file to the database!<br />'
    }
    else {
    }
return $insert;
}

---

Update Queries Do Not Always Update
We are running a MySQL database using PHP to create html pages and forms to update and add information to the db. Sometimes the update query works, sometimes it doesn't - or at least, not permanently. A new record may be added, but five minutes later it has sometimes, but not always, mysteriously disappeared.

Where should I look for a solution? Is there a cache that MySQL creates that may be being accessed unwittingly?

We are using the standard php and MySQL versions that come as standard on a Mac OS X Tiger 10.4.7 server. The browser we are using is Firefox 2.0.4.

---

Need To Update PK Of Every Row
I want to update every row's primary key and need an SQL statement to do it.

There are 2 columns
PK
Placename

So I firstly want to order all the rows by placename in alphabetical order and then starting by updating the first row to 1, I then want to update every ID with an increment of one - all the way until the final row is updated.

---

Log Update In My.ini
If i put

log-update=update

in my ini it works fine,but as soon as try to specify a different location i.e.

log-update=/backups/update

i cannot access mysql. I have a folder called backups in the data folder and one in the MySQL Server 5.0 folder (just incase), but it still will not work.

---

Update If?
I have a table that has an item field, a price field, a price min field, a price max field.

I want to be able to store the current price for an item as well as it's high and low. Is there a way to do an update on the row for the new price as well as compare the new price to the min field and max field and then update them if the new price is either higher or lower than the current values in the min and max field?

I know how to do it with multiple queries and comparing, but am looking for a clean way in one query.

---

Last Update
Is there a way in mysql to discern when the last date/time a field, row, or
table was been updated or inserted?

---

Can't Update
I'm current using MySQL and ASP.

But there seems to be a problem when i try to update a particular record. I get the following error message.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
Query-based update failed because the row to update could not be found.

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM MainPageContents " & _
"WHERE BelongsToShop=" & Session("ShopID") & _
" AND ID=" & which

rs.Open sql, conn, adOpenKeyset, adLockOptimistic

rs.update
rs("some_fld") = "something"
rs.update

I tried setting cursor locations to aduseclient and aduseserver but to no avail. There's also nothing wrong with the query. Does anyone know if there's any kind of issue on updating MySQL using ASP?

---

Getting A SUM In An UPDATE
I'm trying to update a "parent" table with the sum of data in a "child
table". To illustrate, let's say our database was modeled around a book:

book
-> chapter
-> page
-> attachment (pictures)

The -> represents a one-to-many relationship (e.g. a book has chapters).
Each attachment has a filesize and the parent book has a total_size
(denormalized) column that is the sum of all it's children attachments. I'd
like a query to update total_size in the book table.

Below is what I've come up with thus far. Obviously, it's not working, and
hence my post. The nested SELECT query works fine, but I'm receiving a "You
can't specify target table 'book' for update in FROM clause" error. Code:

---

Update SQL
I am trying to code a bit of a java app up, and I am sort of learning as I go, so please bear with me for my lack of experence.

I set a table in a database using the following syntax:

CREATE TABLE Instructor(
       FirstName         VARCHAR(20)      NOT NULL,
       Surname           VARCHAR(20)      NOT NULL,
       Title             VARCHAR(20)      NOT NULL,
       DOB               DATE         NOT NULL,
       Address1          VARCHAR(50)      NOT NULL,
       Address2          VARCHAR(50)      NOT NULL,
       Address3          VARCHAR(50)      NOT NULL,
       PostCode          VARCHAR(50)      NOT NULL,
       Telephone         INT          NOT NULL,
       Mobile            INT          NOT NULL,
       Email             VARCHAR(50)      NOT NULL,
       
       PRIMARY KEY(FirstName, Surname));

I create a record with some information (firstname = emma, surname = newton)...

I am then trying to update a record that has been created as follows: Code:

---

How To Get Key ID For An UPDATE
I need to update a series of fields of a table based on some conditions and get the primary key of affected fields for post-processing the updated rows. Due to the fact that update conditions varies, I do not know what the key field(s) is(are) in advance, so what I want is, when making the update, and in the same transaction, get the primary key of the row that was updated.

What I'm doing now is going through a series of 2 queries per transaction, the update and then a select searchig for the updated row(s) with the updated field content.

I'm working in PHP 5.1.6 under fedora 6. In a worst case, the update will affect up to 10 rows, so I'll need to get all 10 primary keys... the query I'm running is:

$db_query_p3 = "UPDATE message.outgoing SET
pool_lock = '$p3_lock',
engine = '$engine_id',
pool_engine = '$pool_engine'
WHERE pool_status='10' AND (pool_lock < '$lock_expired' OR ISNULL(pool_lock)) AND ISNULL(engine) AND ISNULL(pool_engine) LIMIT 10";

but how to capture the primary key?

When making an insert the transaction return the key with something like:
$insert3 = (@dba_insert_id($db_query_p3))

---

Update
I want to update 8 rows in a table but these are spaces over the 25 tables. There is a common factor in them all but can't think of a way of doing it.

---

UDF With Update
I have a lookup table that holds two columns - a key value and another
column containing items. For e.g.

Col1 Col2
1 10
1 15
1 12
2 20
2 15

Anohter table has to be updated with col1 and the max value of col2
for every col1. So ti should look like
1 15
2 20
....

Right now i have a sQL statement that gets the max of the first table
and then does an update on the second. I was thinking about making it
a single query. Multi table update doesnt work here as the order of
updates is not guaranteed.

So i wrote a UDF that will get the maximum value of a column for any
table. So if u call maxfun('Tab1', 'Col2', 1) it will return 15(The
maximum value of col2 in Table1 where col1 = 1).

When i use this in my update
update table2 set column = maxfunc('Tab1', 'Col2', 1)
where column2 = 1;
hangs mysql.
Has anyone tried UDF with update statements?

---

Update
Is it possible to do update a row with out first doing a separate query... sort of like += in perl or the sum() function?

---

UPDATE
I have in table 2 rows with same record only field 3 and 4 is different.

field 1, field 2, field 3, field 4
_______________________________________
salcin, elva, 23, 0
salcin, elva, 0, 45


so how can I update or... to have only one row with record like:

field 1, field 2, field 3, field 4
_________________________________________
salcin, elva, 23, 45

So just to get 1 row and where is value 0 and field 1 and 2 same to get "new" value

---

UPDATE
I have in table 2 rows with same record:

field 1, field 2,  field 3,   field 4
_______________________________________
salcin,   elva,     23,         0
salcin,   elva,      0,         45

so how can I update or... to have only one row with record like
field 1,  field 2,  field 3,   field 4
_________________________________________
salcin,    elva,     23,         45
     

---

Update
"UPDATE `ItemInformation` SET `LastItem` = 0, `ItemUsed` = 0 WHERE `GameNumber` = $row[0] AND `TeamNumber` = $row[1] AND `LastItem` = 1";

With this code, it's updating `LastItem`, but not `ItemUsed`. Can anyone point me towards a indication as to why?

---

Update 5.0 To 5.1 Or 6.0 ???
i just upgraded to latest mysql 5.045, should i go to 5.1 or 6.0?? is it worth the pain, do i need to change passwords format like from 4 to 5?

---

Update
I have about 1000 updates required on a table.

The syntax i know is for example

Update t1 set b_id=9264 where b_id=4259;

and then the next one would be

Update t1 set b_id=9358 where b_id=2253;

etc etc

Is there any way i can run an update with multiple instructions?

---

UDF With Update
I have a lookup table that holds two columns - a key value and another
column containing items. For e.g.
Col1 Col2
1 10
1 15
1 12
2 20
2 15

Anohter table has to be updated with col1 and the max value of col2
for every col1. So ti should look like
1 15
2 20
....

Right now i have a sQL statement that gets the max of the first table
and then does an update on the second. I was thinking about making it
a single query. Multi table update doesnt work here as the order of
updates is not guaranteed.
So i wrote a UDF that will get the maximum value of a column for any
table. So if u call maxfun('Tab1', 'Col2', 1) it will return 15(The
maximum value of col2 in Table1 where col1 = 1).
When i use this in my update
update table2 set column = maxfunc('Tab1', 'Col2', 1)
where column2 = 1;
hangs mysql.
Has anyone tried UDF with update statements?

---

Update
I have an address database "address", and I need to update the field "street" row by row for some reason, then when I use

update (select street from address limit 0,1) set street="High Road";

I have a error

nor

update address set street="High Road" where Street= (Select Street from address limit 0,1).

---

Update 4.x To 5.0
Has anyone made this update?

I'm running on FC4 for our corporate intranet and am running the non-rpm distro of MySQL 4.1.

What is the easiest way to update to 5.0? I don't really see any update scripts or anything to make this update? Is it as easy as untarring the package - installing it just like I did 4.1 and then copy the data files over (certainly it can't be that easy?

---

Update
[MYSQL]

"UPDATE `ItemInformation` SET `LastItem` = 0, `ItemUsed` = 0 WHERE `GameNumber` = $row[0] AND `TeamNumber` = $row[1] AND `LastItem` = 1";

[/MYSQL]

With this code, it's updating `LastItem`, but not `ItemUsed`. Can anyone point me towards a indication as to why?

---

Update
I want to update the country field in my clientinfo table with phpadmin where country is empty.
The field is:
Name:country
Type:varchar(250)
latin1_swedish_ci
Atributes: -
Null: Yes
Predetermined: Null

What is the mysql to do that?

---

Update
I have two tables status and confirm status.
Status has these fields
authno,user,status

confirm_status
authno,user,status

now i want to do a update script that updates the status in the status table based on matching authno from confirm status???

so im trying to update the status field in the status table based up matching authnos from confirm_status and the status table??

---

Update
an user can have 1 to 5 photos in his profile. One of them is primary. (row 'first' = Y)
When the user deletes his primary photo and have 3 other photo's then one of the 3 photo's must be updated as a primary photo.
The basic query:
UPDATE user_fotos SET first = 'Y' WHERE userid = 10

But, when the user 10 has 3 photos then all the 3 photos in the user_fotos table will be updated with first = Y. So i wonder if it's possible to do a query like this:

Quote:
UPDATE user_fotos SET first = 'Y' BY ONLY ONE ROW WHERE userid = 10

---

Update Changes Id Key
how/why "update" cahnges my primary key (called Id as most people do). i don't know if this can becoem a problem (well it hasnt for me yet) ?

---

UPDATE Doesn't Update
I use mysql minimally. did a search here but did not find anything.
if the column data in the UPDATE command is exactly the same as the column data that is in the table, the mysql_query returns a 0 and (apparently) does not update the table.
is this true?

---

Update
I need to find a way to, well, what would in effect be running many "update" queries in one. heres what im trying to do:
I have a column, name it col1. It has data in it already, I have added a new column, name it col2. I need to copy all info in col1 to col2 on every row. Short of running an update query for every row in the table, I dont know how to do this.

---

UPDATE
Is there a way to UPDATE or INSERT a row into a table?
Like: "UPDATE INTO table SET value1 = value1 + 1 WHERE uniquekey = $criteria",
which will either update an existing row or create a new row, if the row doesn't already exist?
The other way would be to check, if there's already a row for $criteria, and then either UPDATE or INSERT the data.

---

UPDATE
I'm trying to get all my banktransactions (from csv-files from my bank) into MySQL (5.0.33). I managed to do that. Now I have to update the 'target'-base because some fields don't have the right values (ie INC should be changed to Incasso and 087xxxxxxx should be 87xxxxxxx)
I tried to make a  1 query that updates all the fields (columns) I want, but thats not working. I can write multiple queries that each change a value but all those queries should be run twice every month.
So, but i'm not sure, I thought this was something to do as stored procedure. Am i right or is there a better way?
If so I have to read about stored procedures and try to figure out how to work with them.
But first of all I have to know if 'stored proc.' are the way to handle this.

---

No FTP To Update
I want to know is it a good approach I store all information of my web pages in a mysql database and retrieve them by a php script
and echo html code of each web page,
In this way I can update my web site by my management php script without using ftp. but I'm not sure about it's over head and
mysql traffic when a large number of users view my web site!

---

Sql Update
I have the following SELECT statement which returns me data as wanted.

SELECT DISTINCT F0301.A5AN8 FROM F0301,F0150 WHERE F0301.A5AN8=F0150.MAPA8 AND F0301.A5SITO='P'

How to change this to an UPDATE statement? I want to update the A5SITO='P' to 'C'.

---

Update Pk Idx(int)..
I want to update pk key (int, auto_increment)
so I send query like this

update board set idx = (select max(board.idx)+1 from board) where idx = 500;


I get log

Error Code : 1093
You can't specify target table 'board' for update in FROM clause

---

Update All
I have been searchinf for this for ages on the net and not found anything useful.
I want to be able to update 3 rows in a table which has 6 rows. There are an un-specified number of columns. Basically I want to be able to reset all the value in 3 rows back to zero.

---

Update SQL
My old MySQL won't support subquery, but I wish to do sth like this:

update album set title = (select title from old_album where album_id = '123') where album_id = '123'
[ i.e. to copy the title from OLD_ALBUM to ALBUM for id=123]

and I have a constraint that my SQL have to start with
update album set title =

---

Update
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement:


UPDATE ApplicationTbl
INNER JOIN AcademicTbl
ON ApplicationTbl.CampusID = AcademicTbl.CampusID
AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period
SET ApplicationTbl.App_Status = 'Qualified'
WHERE AcademicTbl.Sem_OnCampus >= '1'
AND AcademicTbl.GPA >= '2.4'
AND AcademicTbl.Judicial_Sanction IS NULL

It keeps saying its wrong. Even though I know its not

---

Update Does Not Update
my query:

PHP Code:

 UPDATE `users` SET `confirmed` = 'YES' WHERE `code`=' efd5ed6847d337a289397b864a034a91' AND `user`= 'boarderstu' 


does not give any errors in phpmyadmin, but it doesn't change the value of confirmed either

---

Update Query
I have a query question. I want to update a database that contains ips like 90.30.100.xxx. I only want to modify the third numer (100). Does anybody know how can i do this?

ex: i want to modify all ips from 90.30.100.xxx to 90.30.101.xxx. Notice if i had 90.30.100.100 i'd like to become 90.30.101.100

---

UPDATE Question
I have a table:

|----------------|
|Page
|----------------|
|int id
|text content
|int position
|----------------|

Pages are sorted by position values.

I want update every row of this table with one sql call so that position values will be compressed.

before:

id content postion
----------
12 dfsdf 5
62 sdfsf 10
81 sdfds 19
55 sdfd 21

after:

id content postion
----------
12 dfsdf 1
62 sdfsf 2
81 sdfds 3
55 sdfd 4

---

UPDATE Question
Having some problems with a query, what I want to do is to update a value in a table, if the new value would be less than 0 I want it to be 0. I tried something like this:

UPDATE table SET value = (
SELECT
IF( (@amount := (
SELECT value
FROM table
WHERE id_one = 1
AND id_two = 2 ) -50 <=0 ) , 0, @amount
)
)
WHERE id_one =1
AND id_two =2
The error it gives me reads as

#1093 - You can't specify target table 'table' for update in FROM clause
Anyone knows how to solv this or maybe make a way better query altogether?

---

UPDATE Or INSERT
Kind of worked myself into a weird place in my current application. Where I find myself at is with a single form that is supposed to handle both updates of existing rows and insertions of new rows. The way I want this to work is:
1) If the ID is specified and references a valid row, update that row
2) If the ID is specified but does not reference an existing row, insert a new row
3) If the ID is not specified, insert a new row

Now, (3) is easily done in my application - just test if the ID was specified by the user. Simple. Nothing to it.

What I'm wondering is if there is a way to do both (1) and (2) in a single MySQL query and receive feedback such that I can in my application inform the user whether an update or an insert took place. I could do this with two seperate queries (query for the ID; if I find the row update it, otherwise insert a new one), but I'd like to do it with a single one if possible (mostly because I want to expand my skills with complex SQL queries).

---

SELECT And UPDATE
I was wondering if it is possible to combine a select and update command.

Basically selecting a single row and returning it; while updating a value in that same row?

---

UPDATE Problem
So i've got this query where i'm trying to update a value in a table (along with php)

$pic1 = $_SESSION['time_pic1'];

$id = $_GET['id'];

$query = "UPDATE db.people SET people.pic1=$pic1 WHERE people.user_id=$id";

$results = mysql_query($query)
or die(mysql_error());

When i echo $pic1 i get "photos/321564897pic.jpg" which is the correct value
and when i echo $id i get "000124" which is the correct value as well.

The query though returns: Unknown column 'photos' in 'field list'

---

Update Or Insert Sql
I am using this query to update tableone based upon the values in tabletwo. I really like this query because it works very fast.

PHP

UPDATE tableone n, tabletwo c
   SET n.sec  = c.sec,
       n.cat = c.cat,  
       n.published = 1
WHERE n.section = c.section
   AND n.category = c.category


This works great however I cant seem to get it to insert or append if the data already exists. It just overwrites the data.

My difficulty is that I need certain columns to be updated (overwriting the data) and other columns to be inserted or appeneded to (adding to the data).

Is a stored procedure the best way of doing this?

Or, is there a way of adapting my sql to cater for this?

---

Update 175,000 Records In 1 Hit
I have just under 175,000 records in my database and need to create an MD5 hash of a certain field in each row.

I created a page to run on localhost which had the following
PHP

<?php mysql_connect("localhost", "root", "") or die(mysql_error()); mysql_select_db("wordlist") or die(mysql_error()); if (isset($id)) {     $id=$_GET['id']; } else {     $id=&#391;' } $GetThisWord_result = mysql_query("     SELECT *     FROM word_list     WHERE id=$id") or die(mysql_error()); $GetThisWord = mysql_query($GetThisWord_result); $GetThisWord = mysql_fetch_assoc($GetThisWord_result); $word=$GetThisWord['word']; if ($word=="") {     header("Location: ./end.php"); } else {     $query = "UPDATE word_list SET hash=MD5('$word') WHERE id='$id'";     mysql_query($query) or die(mysql_error());          $nextid=$id+1;          header("Location: ./index.php?id=$nextid"); } ?>


But the browser keeps coming up saying theres an error as the page will just keep repeating itself after it's processed 20 rows.

Can I just enter
PHP

UPDATE word_list SET hash=MD5('$word') WHERE id='$id'

or simiilar into MySQL Front and let it do it all itself?

---

Help With Update Syntax
This is a fairly common task, but for some reason I'm blanking and haven't had any luck with the manual.

What I want is a query that tries to update a record on a table, or if the record doesn't exist it inserts it.

Is there a single query way to accomplish that?

---