Update Not Working

[warrenk]

I am trying to update table supplement with the lowest price and company found in suppprice and I get errors. Any idea why?

SQL query:

UPDATE supplement set( supp_lprice, supp_lcomp ) = ( SELECT min( suppp_price ) , company_id
FROM suppprice
WHERE supplement.supp_id = suppprice.supp_id
GROUP BY supp_id )

MySQL said:

#1064 - 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 '(supp_lprice, supp_lcomp) = (select min(suppp_price),company_id from suppprice ' at line 1

 

[mrdenny]

You might want to post that in the MySQL forum forum436.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LKBrwnDBA]

Maybe I got the wrong MySQL syntax, try this:

UPDATE supplement, 
     ( SELECT min( suppp_price ) As supp_lprice, company_id, supp_id
         FROM suppprice GROUP BY company_id, supp_id) As suppprice
   SET supplement.supp_lprice = suppprice.supp_lprice
     , supplement.supp_lcomp  = suppprice.company_id
 WHERE supplement.supp_id     = suppprice.supp_id

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb