I am new to MYSQL and this forum, so please bear with me. I have been trying to figure this one out, but have so far drawn a blank. If anyone could help me out with the right SQL syntax for an Update Query, I would be most grateful, thanks.

I have two tables, A and B.
Table A contains:
match_key
corporate_stays
Table B contains:
match_key
corporate_stay_category

For each record on Table A, there will be one or many table B records, using the match_key as the join on both tables. Each matching Table B record will have either corporate_stay_category = "C", or blank (Not Null)

I want to be able to Update the corporate_stays on each record on table A, with a count of matching table B records that have a corporate_stay_category = "C".

what version of mysql are you using? Multi table updates are supported in 4.0.4 and newer IIRC.

Recently upgraded to MYSQL v 4.1.17!

why are you trying to update a value based on data within the database. You should be able to get to the value using a query. Otherwise you run the risk that the information in the table will be out of date

What you are looking for is a relatively simple query
select count(<column to count>) NoStays from
<table>
<specify the join>
<specify the where criteria>

the join will probably be
Join A.match_key on B.match_key
group by a.match_key
having corporate_stay_category="C"
or somethign similar - sorry away form the Linux / MySQL box today so can't test it.
If all else fails look in the manual for the join syntax and SQL aggregate functions, or dare I say it get a decent SQL or MySQL reference.

I have already achieved the SELECT, and I appreciate that the data will get out of date. (Our database is updated once a month by importing data from the client, it's not a transaction database, but a marketing database that is used for statistical analysis)
I will need to do several of these updates each month to classify each record on table A. These classifications are then used to select certain categories of records for direct mailing.
Hope that I have explained this succinctly, in essence it is important that we carry out these updates once a month.

clive, i've tested this a few ways from sunday, and the multi-table update syntax doesn't seem to like the GROUP BY clause

you will have to do the aggregation of B rows into a temp table, and then do the update from that