开发者

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/).

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜