Compare values in two tables and update table1 if table2 has a higher value

I'd like help with a mysql query that:

• For the max(id) for each transaction.position rows where t_type = 'buy'
• Gets the most recent (by date) price from the quotes2010 table (based on the transactions.symbol)
• IF the price for the symbol is higher than the price in the transactions.high column
• Subtract 0.01 from the higher quotes2010.price
• Update the higher price in the transactions.high column

. . . . in a single query.

The most basic thing I'm trying to do is UPDATE the a table IF the price for the symbol in the 2nd table is higher. Here is a basic version of the database structure:

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `symbol` char(8) NOT NULL,
  `high` double(8,2) NOT NULL,
  `t_type` enum('buy','sell') NOT NULL,
  `t_date` datetime NOT NULL,开发者_JAVA百科
  PRIMARY KEY  (`id`),
  KEY `position` (`position`,`id`),
  KEY `t_date` (`t_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `transactions` VALUES(1, 'AO1', 1.35, '2010-12-12 00:08:57');

CREATE TABLE IF NOT EXISTS `quotes2010` (
  `symbol` char(8) NOT NULL,
  `price` double(8,2) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`symbol`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `quotes2010` VALUES('A01', 1.40, '2010-12-19 10:03:05');

Your schema and dataset are limited, but I think this will do what you need.

UPDATE 
quotes2010 INNER JOIN
(
    SELECT symbol, MAX(date) AS max_date
    FROM quotes2010
    GROUP BY symbol
) quote_by_maxdate ON quote_by_maxdate.symbol=quotes2010.symbol AND quote_by_maxdate.max_date=quotes2010.date
INNER JOIN transactions ON quotes2010.symbol = transactions.symbol
INNER JOIN
(
    SELECT symbol, MAX(id) AS max_id
    FROM transactions
    WHERE t_type='buy'
    GROUP BY symbol
) transactions_by_max_id ON transactions.id=transactions_by_max_id.max_id 
SET quotes2010.price = IF(quotes2010.price > transactions.high,quotes2010.price-0.01,quotes2010.price),
transactions.high = IF(quotes2010.price > transactions.high,quotes2010.price,transactions.high)