I tried doing the old updateable view thing, but that wouldn't work because of a union in the view definition...

Here's a suggestion...

You'll need to create a temporary table (or table variable) as a middle ground for sorting, and then update the base tables. Here's some sample / demo code that shows the solution I have in mind:

USE tempdb;

CREATE TABLE t1 (id INT, description varchar(MAX));

CREATE TABLE t2 (id INT, description VARCHAR(MAX));

GO

INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c'),(80,'eighty');

INSERT INTO t2 VALUES (12, 'twelve'),(58,'58'),(70,'seventy'),(92,'92');

DECLARE @ttemp TABLE (id INT, description VARCHAR(MAX), rownum INT NULL) -- or use a temporary table...

INSERT INTO @ttemp (id, description, rownum)

SELECT foo.id, foo.description, ROW_NUMBER() OVER (ORDER BY foo.id) FROM (

SELECT id, description FROM t1

UNION ALL

SELECT id, description FROM t2 ) foo;

--SELECT * FROM @ttemp

UPDATE t1 SET id = ttemp.rownum

FROM @ttemp ttemp

WHERE dbo.t1.id = ttemp.id

UPDATE t2 SET id = ttemp.rownum

FROM @ttemp ttemp

WHERE dbo.t2.id = ttemp.id

SELECT * FROM dbo.t1

SELECT * FROM dbo.t2