SELECT And UPDATE In One Query

Select And Update In One Query
My remoteurl table has 3 columns: id, hits and url.

PHP

UPDATE remoteurl SET hits=hits+1 WHERE id=2

SELECT url FROM remoteurl WHERE id=2

Instead of using two queries I want to use one query which could select url and update hits column. Is it possible?

---

Query (select/insert/update)Slows When Table Grows
I have a mysql database where tables are very simple. An example table will look like this:

create table myTable(
token varchar(255) not null primary key,
token_count int default 1,
frequency int
) type=INNODB;

A java program populates this table. The program reads from files (in batch mode) and inserts into myTable with following logic:

(1) See if the String to be inserted is present in myTable.(Uses SELECT)
(2) If not present the insert into myTable (uses INSERT)
(3) If yes update the table after adding the current frequency (USES UPDATE)

After processing each file issue COMMIT.

This goes on well for some time - but after the Table grows over 100,000 rows the process slows down considerably. The Strings I 'm handling is very large and I'm not sure how well Mysql behaves with very large String as primary Key.

My select and update statements are all based upon primary key(token in this case) - so question of setting index doesn't arise. Even then I have set index on token field - but it has degraded the performance. Also I have set the query_cache_size to 1MB even then it is not enhancing the performance.

A sample file has almost 30,00 to 50,000 lines. Each line having a string + blank + frequency.

After processing first few files - the Java program takes large time to process a single file - and subsequently the processing time increases exponentially.(as the tabel grows in size it slows down).

---

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.

---

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 &amp; Sub-select
I'm getting the error msg "you can't specify the target table 'orders" in the from part of an update" when executing the following query

update orders set status='READY'
where status='PENDINIG' and parent_id in (
select p_orders.id from orders p_orders where p_orders.status='OK')

Does this mean that I can't do a sub-select on the same table as I'm updating in MySQL (I'm using ver 5.x) ?

Would anyone know a way around this - I'd basically like to update an order once its parent order is in the state 'OK'.

Ref:

create table orders (
id bigint(18) unsigned not null auto_increment,
status varchar(32) not null,
parent_id bigint(18) unsigned,

constraint fk_order_parent foreign key (parent_id) references orders(id),
primary key (id)
);

insert into orders values (null, "SENT", null);
insert into orders values (null, "OK", null);
insert into orders values (null, "PENDING", 2);

---

UPDATE Using SELECT
Coming from an Oracle background I am used to doing this:

UPDATE accomodation a
SET a.country = (SELECT c.country
FROM country c
WHERE c.country_id = a.country_id);

I could not get this to work so looked up the syntax for this:

update accomodation a
join country c on a.country_id=c.country_id
set a.country=c.country;

Which doesn't work either !!! Basically I am trying to do an update using a select from another table rather than a static value but cannot get it to work. Is this possible in MySQL ?

---

Update And Select...
Can I do an update and select in the same query?

I want to show a record and also update the "times it was viewed"...is that how I'd do it?

---

Update And Select
an update that doesn't affect any records. As a test, I switched it to a Select statement. Both statements have the same where clause. The select statement returns the number of rows I expected to update. Here are both queries. Any help greatly appreciated.

UPDATE program_event set measure=239, measure_date='2007-01-25' WHERE program_goal_id = 112 AND measure_date >= '2007-01-22' AND measure_date < '2007-01-27'

select * from program_event WHERE program_goal_id = 112 AND measure_date >= '2007-01-22' AND measure_date < '2007-01-27'

---

Update And Select
Code:

"UPDATE `links` SET
`in`=`in`+1
WHERE `url` LIKE '%google.com%' LIMIT 1"

alright, so i want to a) update the link to in+1, but also b) return a variable called 'user' containing the 'id' of the link, all in one statement because it is faster and gives me the same link's id that was updated. is it possible?

---

Select + Update
I managed to do something like this:

$query = sprintf("SELECT CONCAT('UPDATE articles SET visits=(visits+1) WHERE title='".$name_article."'')
FROM articles a
LEFT JOIN autors b ON a.autor = b.name
WHERE a.title='".$name_article."'");

$result = mysql_query($query);

Without the part underlined, it works pretty good. What I want is to be able to select all columns from the article table and autors table, based on the article name, and, at the same time, update the visits field in the article table. I really dont know if its even possible to do that in one single query.

---

SELECT But Not UPDATE
If I have used SELECT to review a row of data from a table and then only want to allow it to UPDATE certain fields, even if the other fields were changed, would I use the UPDATE command, and choose which fields can be updated? Will this stop the other fields from being updated? I am trying to prevent people from changing certain values in the table.

---

SELECT And UPDATE
I'm a newbie in MySql, so please bear with me. What I want is to select all the informations concerning a specific article, and then to update a field of the resulting query. So, with some help I managed to do something like this

CODE$query = sprintf("SELECT CONCAT('UPDATE articles SET visits=(visits+1) WHERE title='".$name_article."'')
                 FROM articles a
                 LEFT JOIN autors b ON a.autor = b.name
                 WHERE a.title='".$name_article."'");  

$result = mysql_query($query);

---

Select Update
I am trying to populate a column in one table using a calculation from values in another table.I have got something like this.
Code:

update orders_header
set od_gross = (SELECT sum((price * qty) + vat) FROM orders_detail)
where orderref = 'COR0010-000061'

---

SELECT And UPDATE In One Order.
i would like to select some random lines from a table and count that they were selected.

My method:

SELECT * FROM table ORDER BY RAND(Now()) LIMIT 50;
while ($row = entry) do
UPDATE SET Used=Used+1 WHERE Id=$rowd[Id]

I dont think this is realy fast so i dont like it, is there a faster method?

---

Select + Update In One Statement
I have these statements.

$selectdata = "SELECT * FROM imagetrack WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'";

$query = mysql_query($selectdata) or die(mysql_error());
$update = "UPDATE imagetrack SET seen = seen+1 WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'";

Basically i was wondering if there was a statement where i can select everything and update the seen in one statement.

---

Atomicity Of A SELECT/UPDATE
I want to get any one of a number of rows that matches some criteria and
update it so that it is marked as 'mine'. How can I do this safely?

Given something like this table:

create table tbl_new (
t_value varchar(16) primary key not null,
dt_used datetime,
l_pid int(9)
);

I want to get any t_value WHERE dt_used is null and then set dt_used to
prevent anyone else getting the same t_value.

If I use locking (but I'm using C and I don't see any locking functions)
(nor a START TRANSACTION?) I could: Code:

---

Select And Update Statement
Is it possible to combine a select and update statement in mysql? If
so, what is the syntax.

---

Select And Update Concurrently
I have a db used for shop floor data collection that I have converted from a Progress V8 RDBMS. It is recording some 50 data points from each of 6 shop floor machines every 5 seconds. The problem I have is one of incremental backup of this (the main data logging) table. In the old system, there is a LOGICAL (boolean) flag field that is set (in code) when the data records have been copied to the backup file (could be another table). I can't seem to find in MySQL documentation or in these forums, a way to select and update in the same query.

What I would like to do (in as plain english as possible) is:
Select all records from the source table where the 'copied' flag is not set, and send them to a destination (which could be a flat file or another table), simultaneously setting the 'copied' flag in the source table.
I thought of doing this in two passes, selecting the records first, and outputting them, then setting the flag, but the problem this poses is that there may (probably will) be fresh data written to the table during and between the passes. This will cause records to flagged as copied that have not actually been backed up.

---

Update With Embedded Select
What is wrong with this syntax ?  Using MySQL 3.23.51-nt
UPDATE products SET manu_release_date = (SELECT dates.reldate FROM dates, products WHERE dates.id = products.stock_code )
I get the error:
Error MySQL said:

You have an error in your SQL syntax near 'SELECT dates.reldate FROM dates, products WHERE dates.id = products.stock_code )' at line 1

---

Select, Update, Insert. Efficiently?
First, I want to say that I didn't design the databases and I wish I could fix them, but I can't.

There is a table, s01_Baskets, that tracks the users basket throughout my store.
There is a table s01_StoreKeys, that holds the next ID for every field used in tables. In this case I'm interested in is the basket_id.

What I need to do is create a new "basket" for a customer. To do this I need to select the key for this basket_id:

SELECT
s01_StoreKeys.maxvalue
FROM
s01_StoreKeys
WHERE
s01_StoreKeys.type = 'Baskets'
Then I can "create" a basket by inserting a row:


INSERT INTO
s01_Baskets
(
s01_Baskets.session_id
, s01_Baskets.cust_id
, s01_Baskets.basket_id
, s01_Baskets.order_id
, s01_Baskets.order_proc
, s01_Baskets.last_update
, s01_Baskets.ship_id
) VALUES (
'".session_id()."'
, &#390;'
, &#390;'
, &#390;'
, &#390;'
, '".microtime()."'
, &#390;'
)";
Then I have to update that field for next key for the next basket.


UPDATE
s01_StoreKeys
SET
s01_StoreKeys.maxvalue = '".$basket_id+1."'
WHERE
s01_StoreKeys.type = 'Baskets'
UGH. I'm not thrilled doing it like this (one bad query, or if another process jumps in at the wrong time, I've messed up the baskets....)

Is there a better query for this? I'm pretty sure I can do the insert and select as one, but I need the basket_id(+1) for the update also. I don't know of any way to do all three queries in one. Can anyone see a better way to do this queries (ignore the poor design)?

---

SSH: Select Database And Update Rows
I'm trying to update a few rows in my database via SSH. I have multiple databases on my server and I need to know what I'm doing wrong. I login via SSH and enter

$: mysql -u root -p

I want to select the database and then run my update command, but when I type. > select [db_name] It just jumps to a new line without any prompt. Can anyone tell me really quick when they do to select a database?

---

Using Limit With Select Or Update Command
How come I can do:

select field1 from database limit 10
*shows first 10 records like it should

select field1 from database limit 1,5
*It returns first 5 rows correctlly

update database set field1='first set" limit 10
*It correctlly updates the field for the first 10 records

update database set field1="top 5" limit 1,5
*I get an error that says problem with limit at ^5 check command for correct syntax

Is there different syntax for limit when using with a select statement Vs. an update command?

---

Nested Select In Update Statement
my nested Select-Update-Statement doesn't work. I think it's because the SELECT-Statement returns an empty set.. is there a posibillity to run this statement if there is no empty set and insert NULL if it is? Or is there another error at all?

CODEUPDATE proj_user_suche SET acronym = (SELECT name FROM acronym LEFT OUTER JOIN link_acronym ON acronym.id_acronym = link_acronym.id_acronym WHERE projekte_nr="23") WHERE id_proj_user_suche="17"

---

Convert SELECT Statement To UPDATE
How do I convert the following SELECT statement to UPDATE statement? If I'm not mistaken, UPDATE statement could not involve more than one table.

SELECT * FROM F0116 LEFT JOIN F0101 ON F0116.ALAN8=F0101.ABAN8 WHERE F0116.ALCTR='' AND F0101.ABMCU='1'

---

Limitations In Mysql Select/update Statements
I'm working on several tables at one time and once in a while I need to
update them based on a previous select statement.

This ends up with 3-5 statements but every single time I do it this way,
mysql chokes after the 3rd statement, no matter what order.

I'm on Mandrake Linux 9.1, PHP 4.2.3 with mysql 4.0.11a and Apache 1.3.27.
Everything else works like a charm but not these 3-more statements.

---

Limitations In Mysql Select/update Statements
I'm having a little problem here that seems difficult to solve - to me.

I'm working on several tables at one time and once in a while I need to
update them based on a previous select statement.

This ends up with 3-5 statements but every single time I do it this way,
mysql chokes after the 3rd statement, no matter what order.

I'm on Mandrake Linux 9.1, PHP 4.2.3 with mysql 4.0.11a and Apache 1.3.27.
Everything else works like a charm but not these 3-more statements.

I assume it's something about configuration, but unsure.

Any ideas?

---

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 Query
I'm selling tickets and I need to be able to reduce the total number of tickets when one is sold. I have a table with the total number of tickets stored in a field and and a field with the quantity sold in a particular purchase. I think this is a fairly simple update query can anybody help get me started?

---

Update IN Query
Code:

UPDATE SET COUNT=COUNT+1 IN WHERE SELECT
sponsor,mstatus, count( username ) from customer Group by
sponsor DESC Having count( username ) = 2

---

Update Query
I am trying to do a custom sortup/sortdown function in php on a mySQL basis. Now I got that prob:

I got a table, that has a "sort"-field for each data like that:

data | sort
a | 1
b | 2
c | 3
d | 4
e | 5

The output of the data is ORDER BY sort ASC. Now I want the users allow to change the "ranking"/"sorting" themselves that way, that they click e.g. on sort entry d one up.

So the new table should look like that:

a | 1
b | 2
c | 4
d | 3
e | 5

Does anyone know how to do that in one or only a few mySQL queries? I mean I could read out the whole table and then do a php-loop giving new sort-numbers to each data and then update every single data, but I guess there should be an easier way in mySQL. My prob is that I suck big time at mySQL

---

UPDATE Query
Select TransType FROM TransTypeMapping WHERE vTransType=”CANCEL”, into array[0]

For each record from MainTable where TransactionCode = TransType.array[0],
Find record[1] where record[0].Category=
record[1].Category and record[0].Units1 = record[1].Units1

Action: Set fields in MainTable, record[0].vCancel = T; record[0].vMatchingTransID = record[1].vTransID
Set record[1].vCancel = T; record[1].vMatchingTransID = record[0].vTransID

---

Update Query
I am using phpMyAdmin to update table rows where the short_descr field (text) contains no characters.
I can execute this:
SELECT * FROM download WHERE short_descr='';
But this gives me an error:
UPDATE download SET delete=1 WHERE short_descr='';

---

Update Query
Code:

UPDATE users_tickets SET dateResponse=NOW() and Response='sgdgdsgdsfdsfds' WHERE ticketID='1'

i query the same database earlier in the code and it works fine. in phpmyadmin i tried editing it and it worked fine. this is the code phpmyadmin returns when you do it there
Code:

UPDATE `users_tickets` SET `Response` = 'mommy' WHERE `ticketID` =1 LIMIT 1 ;

---

Update Query
i need a query to change my customers_id number from an example
1000549 to 500600 and i made this query that doesn'work :

UPDATE customers SET customers_id = '50%' WHERE customers_id = '100%'

i need to change the first 3 digits from 100 to 50 and the other numbers stays the same.

---

Update Query
Here's my table set up:Code:

USERS user_id,firstname,lastname,tel,institution,email,username,password
BOOKS book_id, user_id*,created,author,subject,title,description
BOOKCHAPTERS chapter_id, book_id *
CHAPTERS chapter_id *, chapter_name, chapter_location

Im trying to update the chapter_name and chapter_location in the chapters table. The parameters I pass in seem to be ok, so I think it's the SQL update query below.
Have I done the update query correctly?Code:

update chapters c, bookchapters bc, books b set c.chapter_name = ?, c.chapter_location=? where b.book_id = ? and bc.book_id = b.book_id and c.chapter_id=? and c.chapter_id = bc.chapter_id;

---

Update Query
Can we update row when there is an empty field?
here is an example (attached):

How can I update the x?
Let's say x = 6

Like:
UPDATE table
SET [all_empty_fields]='x'
WHERE [field_before_that] ='5';

---

Update Query
I have the following query but I need some help on its structure (for example where do the joins come in):

update new_residence, new_respolis, new_district, new_person
set new_residence.respolis_id = new_respolis.id
where new_person.poll_number = new_respolis.prov_poll_num and
new_person.district = new_district.abbreviation and
new_respolis.prov_elect_dist_id = new_district.id and
new_person.id=1

I tried to run this query but it took forever to execute.

---

Update Query.
I have 2 tables.

BOOKCHAPTERS (book_id,chapter_id)

CHAPTERS(chapter_id,chapter_name,chapter_location)

I want to be able to update the name (chapter_name) of chapters given a book_id. The chapter_id for each book is simply and auto-incremented column.

I currently have the following queery which doesnt seem to work.

Code:


update chapters c, bookchapters bc set c.chapter_name = ?, c.chapter_location=? where bc.book_id = ? and c.chapter_id=? and bc.chapter_id = c.chapter_id;


Now I know you're gonna say 'What's the value of all the prepared values' but I just want to know if the syntax for the query is ok.

---

Slow Update Query
I have about 2000 update queries to do, which takes about 1 hr on 250,000 rows.
My table is getting kinda slow here is the query i am using
UPDATE nametable SET
sectionname = replace(sectionname,'".$oldsec."','".$sec."'),
categoryname = replace(categoryname,'".$oldcat."','".$cat."'), published=&#391;'
where sectionname='".$oldsec."'
and categoryname='".$oldcat."' ;

I am wondering if the same thing is possible with an insert... on duplicate key statement?

I cant seem to get the insert statement to work, but not even sure whether it is appropriate.

With this query I am basically finding and replacing some columns based upon another table (within the php script I am using)

---

Update Query Question
I want to update the col 'type' in one of my tables. I want to change the value from 4 to 5 in that col but the query i use below is not correct. Can anyone point me in the right direction?

Quote:

UPDATE &#55613;&#56984;'
SET type = &#395;'
WHERE type = &#394;'

---

Stuck On Update Query
I am struggling with a SQL statement and hoped someone would be able to help

customer_depots
contacts

Each table contain fields called 'customer_depot_id' and 'address_id'.

What I want to do is update the address_id in the contacts table, with the value of the address_id in the customer_depots table, only where the customer_id values match in each table, AND the address_id in the contacts table is equal to zero.

Does this make sense?

So, update address_id in contacts,
where address_id in contacts = address_id in customer_depots
and address id in contacts = zero.

This is because a number of address_id's are missing from the contacts table, and I want to replace them with the correspondingvalue from the other table.

I have figured out a SELECT statement as shown below, but I need an UPDATE statement as explained above.

SELECT cd.ADDRESS_ID FROM customer_depots cd, contacts con WHERE con.CUSTOMER_DEPOT_ID = cd.CUSTOMER_DEPOT_ID AND con.ADDRESS_ID = 0

---

Help Optimizing UPDATE Query
I have 1 query in my script that is taking waaaaay too much time.

Is there a way that I can speed up or optimize this query?

Right now the query is taking about 600+ seconds to update 500 rows.

The Query

PHP

$update_from_sas_postmeta = "UPDATE wp_postmeta, sas_postmetaSET wp_postmeta.meta_value = sas_postmeta.meta_valueWHERE wp_postmeta.post_id = sas_postmeta.post_idAND wp_postmeta.meta_key = sas_postmeta.meta_key";$update_from_sas_postmeta_result = mysql_query($update_from_sas_postmeta) or die("MySQL ERROR (update_from_sas_postmeta_result): ".mysql_error());

The Tables

CREATE TABLE `wp_postmeta` (
`meta_id` bigint(20) NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL default &#390;',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=294705 ;

CREATE TABLE `sas_postmeta` (
`meta_id` bigint(20) NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL default &#390;',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Sample Data
2, 2, 'page_template', 'default'
3, 3, 'merchant_id', ?'
4, 3, 'merchant', 'SomethingSomething'
5, 3, 'link', 'http://www.website.com/link.html'
6, 3, 'thumbnail', 'http://www.website.com/thumbnail.jpg'

More Details
When the UPDATE query begins to run, the sas_postmeta table will have 500 records in it and the wp_postmeta table will have about 250,000 records in it.

---

Help With Mass Update Query
I have a field in my table (1000 records) called company_number. I've just been informed that all records that start with "1" should actually start with "0". So, for a record where compnay_number = 1000400 the number should actually be 01000400. Can anyone suggest how I would approach updating each record via one SQL statement?

SELECT *
FROM `myTable`
WHERE `company_number` LIKE '%100%'
... then what?

---

Update Value Directly In The Same Query
Is it possible to update a value directly, adding text to it, without getting it first, adding the text in php and then do the update.

for example:
value in field users: ",45,"

and then adding ,34, to the field directly by doing something like:
update table set users='users,34,'

so the value in the field afterwards will be ",45,,34,"?

---

Embed Sum() Within Update Query
I'm currently rewriting my website to work with MySql rather than MSSQL and have stumbled across a bit of a problem. Can anyone help?

To create a simple example of what I'm trying to achieve - I have 2 tables. I wish to populate the SCORE in TABLEA with a sum of all SCORES in TABLEB where the CODE matches. TABLEA has a single row for each CODE but TABLEB may contain multiple rows (for different weeks).....

---

Maximum Query Update?
Is there a maximum amount of fields that you can update at once as run from php the following works:

UPDATE `members` SET
`firstname` = '$formfirstname',
`surname` = '$formsurname',
`usrlevel` = '$formusrlevel',
`usrname` = '$formusrname',
`grade` = '$formgrade',
`phone` = '$formphone',
`email` = '$formemail',
`comments` = '$formcomments'
WHERE `ID` = '1' LIMIT 1 ;


Yet the following does not and it only has one extra field to update:

UPDATE `members` SET
`firstname` = '$formfirstname',
`surname` = '$formsurname',
`usrlevel` = '$formusrlevel',
`usrname` = '$formusrname',
`grade` = '$formgrade',
`phone` = '$formphone',
`email` = '$formemail',
`drivingdate` = '$formdrivingdate'
`comments` = '$formcomments'
WHERE `ID` = '1' LIMIT 1 ;

---

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.

---

Multiple Update In 1 Query
I am trying to do the following using PHP and Mysql:

$sql="
UPDATE records set appear_order='2' WHERE id='19';
UPDATE records set appear_order='3' WHERE id='17';
UPDATE records set appear_order='4' WHERE id='18';
UPDATE records set appear_order='5' WHERE id='20';
UPDATE records set appear_order='6' WHERE id='16';
";

I am getting the following error message:
" Cannot update records: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE records set appear_order='2' WHERE id='19'; UPDATE recor"

I don't know why this isn't working.
when I paste this query to phpMyAdmin (I hope you guys know phpMyAdmin) - it works.
somehow using a PHP code it does'nt - I get that error message.
did anyone every come across this error and knows how to overcome it?

---

Msql Update Query
I am trying to tie a mySQL UPDATE query to a variable in php to determine whether or not the query updated any rows or not. All it returns is whether or not the query was successfully executed.
I have tried using the mysql_affected_rows() command, but as there are multiple users performing the same action this could lead to ambiguous results.

---