Update with a Min in the the table

Question

I want to update a Column in a Table, based on the minimum of another column of the same table.

eg.

JobPositonId | JobPositonName | JobDescriptionId | ContactId
1            | 1              | 1                | 1
2            | 1              | 1                | 0
3            | 1              | 1                | 0

I want to update ContactId to be 1, if it is 0 and where JobPositionId is the lowest.

Answer 1

I think this should work:

update jobTable
   set contactid = 1
 where jobPostitionId in (select pos from (select min(jobPositionId) as pos from jobTable where contactId = 0) as subtable);

It's kind of a hack similar to what's described here (http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/).