How are node and node_revision tables related? I've noticed that the following does not work to unpublish nodes:

db_update('node')
  -&gt;fields(array('status' =&gt; 0))
  -&gt;condition('nid', $array_of_nids, 'IN')
  -&gt;execute();


But it does if you follow up with the same update on the node_revision table. (But perhaps I should only be updating the status of the current revision (vid)?)

I found this confusing because the nodes in question do not use revisioning yet it seems that the revision table holds more authoritative data than the main node table (so if so, what's the point of node table?)

Can anyone explain how node and node_revision are related and therefore how to do a bulk update such as this 'properly'? (I'd prefer to avoid a costly loop of node_load, node_save if poss.) I'd like to understand the Drupal Way here.

(I have read node revisions which covers D6, but that did not give me the understanding I was after.)
The node table holds the basic information about the node, the nid is the primary key on this table and contains the vid field which is a foreign key to the node_revision table. The node_revision table holds each revision of the node, the vid is the primary key on this table and contains the nid field which is a foreign key to node table.

You may think that this information is identical, but it's not. The node table only contains one record relating to a node and also stores the current vid whereas the node_revision table contains the revision data. Just because you're not using revisions it doesn't mean that this process isn't being used.

Also keep in mind that all field tables have 2 versions, field_data_[field_name] and field_revision_[field_name], the data tables contain the current revision and the revisions table contain the revision data.

To get an understanding of how practical revisioning works try looking at Revisioning, a more complex and powerful solution can be achieved using Workbench