INSERT ON DUPLICATE KEY UPDATE

[Aleksandr Guidrevitch]

Hi all !
I'm having problem with INSERT ... ON DUPLICATE KEY UPDATE on mysql 4.1.10
--8<------8<------8<------8<------8<------8<------8<------8<------8<----
INSERT INTO lot_end (id, owner_id, category_id, title, current_price, 
buy_price, end_time, price, bid_count, currency_name)
SELECT lot.id, lot.owner_id, lot.category_id, lot.title, 
lot.current_price, lot.buy_price, lot.end_time, IF(lot.type_id = 2, 
lot.buy_price * exchange_rate.exchange_rate, lot.current_price * 
exchange_rate.exchange_rate) as priceA , COUNT(bid.id) as bid_countA, 
currency.name
FROM lot
   INNER JOIN bid
       ON bid.lot_id = lot.id
   INNER JOIN currency
       ON currency.id = lot.currency_id
   INNER JOIN exchange_rate
      ON exchange_rate.currency_id = lot.currency_id
WHERE lot.id = 1
GROUP BY lot.id
ON  DUPLICATE KEY
UPDATE lot_end.price = VALUES(priceA), lot_end.bid_count = 
VALUES(bid_countA);

--8<------8<------8<------8<------8<------8<------8<------8<------8<----
it reports ERROR 1054 (42S22): Unknown column 'priceA' in 'field list'
Also, I've tried
--8<------8<------8<------8<------8<------8<------8<------8<------8<----
...
on duplicate key update lot_end.price = IF(lot.type_id = 2, 
lot.buy_price * exchange_rate.exchange_rate,lot.current_price * 
exchange_rate.exchange_rate), lot_end.bid_count = COUNT(bid.id);

--8<------8<------8<------8<------8<------8<------8<------8<------8<----
and it reports ERROR 1111 (HY000): Invalid use of group function
Could anyone tell me how to use ON DUPLICATE KEY UPDATE with GROUP BY 
functions ?

Sincerely,
Aleksandr Guidrevitch
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]