I have an instance for a specific content type that at any given time there can only be ONE item promoted to the front page. I have a view that looks for this value and displays the item in a block on the front page if it is promoted. The problem is that it is setup to only show ONE item, so the editor has to remember to go and UN-promote any other node after they promote the one they want. I'm trying to figure out a way that when the user clicks the promote to front page check box on the node edit form and saves the node, all other nodes are auto un-promoted. I tried to hook into hook_node_presave, but I can't seem to get the query right to un-promote all the other nodes, because I am using revisions, so I'm not entirely sure what to update.
This is what I have right now that is not working, I think because I am using revisions:
function MY_MODULE_node_presave($node){
if ($node->type == 'career' && $node->promote == 1) {
$sql = "
UPDATE {node} n
SET n.promote = 0
WHERE n.type = 'career'
";
db_query($sql);
}
}