Update Record With Min Values

[gdtech]

I am trying to update all FIELDs in a TABLE where a condition of min COSTs exist. I currently have 6 rows in my table 2 are non unique when I query select for all min COSTs grouped by ITEM_NO I get 5 - correct. But when I run the update query I get all 6 rows affected.

/*WORKING SELECT QUERY*/
SELECT ITEM_NO , MIN(COST) AS LOWEST
FROM TABLE
GROUP BY ITEM_NO

/* INCORRECT UPDATE QUERY */
UPDATE TABLE
SET FIELD = '1'
WHERE EXISTS (SELECT ITEM_NO , MIN(COST) AS LOWEST
FROM TABLE
GROUP BY ITEM_NO )

Clearly it is not this simple -- please help

[iburyak]

Try this:

UPDATE TABLE
SET FIELD = '1'
From TABLE a
join (SELECT ITEM_NO , MIN(COST) AS LOWEST
       FROM TABLE
       GROUP BY ITEM_NO ) b on a.item_no = b.item_no and a.cost = b.lowest

Good Luck.

[gdtech]

Thanks - worked great