Update with a Min in the table
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.
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/).
精彩评论