IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'node' AND COLUMN_NAME = 'rid')
BEGIN
   ALTER TABLE node ADD rid int
END

-- update node with latest revison
-- set latest revision's id to node
update node
set body = r.body, teaser = r.teaser, rid = r.vid
from node_revisions r, node n
where n.nid = r.nid
AND r.timestamp =
(
    SELECT MAX(node_revisions.timestamp)
    FROM node_revisions WHERE node_revisions.nid = n.nid
)

-- obsolete revisions
select * from node_revisions
where node_revisions.vid NOT IN (SELECT node.rid FROM node)

ALTER TABLE node DROP COLUMN rid