How to update table based on row index?

Question

I made a copy of an existing table like this:

select * into table_copy from table

Since then I've made some schema changes to table (added/removed columns, changed order of columns etc). Now I need to run an update statement to populate a new column I added like this:

update t
set t.SomeNewColumn = copy.SomeOldColumn
from t

However, how do I get the second table in here based on row index instead of some column value matching up?

Note: Both tables still have equal number of rows in their original positions.

Your question is not clear. What do you mean by 'how do I get the second table in here' ? — Shraddha, Jan 05 '12 at 21:20
@Shraddha - Sorry thought it was obvious. Normally, one would use a join or select statement to connect table with table_copy — O.O, Jan 05 '12 at 21:23
There is no **row number** or **row index** in a SQL Server table. You need to have a valid primary key and compare rows from two tables based on their primary key - that's the way to do it in SQL ! — marc_s, Jan 05 '12 at 22:00

score 3 · Answer 1 · answered Jan 05 '12 at 21:19

If you have an ID on it, you can do this:

update t set
    t.SomeNewColumn = copy.SomeOldColumn
from
    table t
    inner join table_copy copy on
        t.id = copy.id

If you have no way to uniquely identify the row and are relying on the order of the rows, you're out of luck, as row order is not reliable in any version of SQL Server (nor most other RDBMSes).

score 3 · Accepted Answer · answered Jan 05 '12 at 21:22

3

You cannot join the tables without a key to define each row uniquely, the position of the data in the table has no bearing on the situation.

If you tables do not have a primary key you need to define one.

answered Jan 05 '12 at 21:22

Tony

9,672
3
47
75

Why? I was hoping this was not the case. – O.O Jan 05 '12 at 21:26
@subt13 - Because row number is not reliable in SQL Server (or any RDBMS, really). Rows are constantly repositioned physically to optimize it. The same `select * from table` query can produce 20 different orderings for 20 different executions. I've had a table before constantly transpose two rows between executions. Row index is, simply, not reliable and there's no (easy) mechanism for you to join on it for good reason. – Eric Jan 05 '12 at 21:29
2

@subt13 - The order of table data cannot be guaranteed unless you specify an `ORDER BY` clause in your query. Although it may appear your data is returned in the same order that may not always be true. Have a look at this answer to another question on SO: http://stackoverflow.com/a/2040833/243925 – Tony Jan 05 '12 at 21:29
@Eric - Does that mean using row_number() over(order by blah) is not reliable as a temp pri key? – O.O Jan 05 '12 at 21:32
@subt13 - If you have rows that tie in the `order by` condition, then yes, that's exactly what it means. See the link in Tony's comment for an experiment you can run to see those types of changes. – Eric Jan 05 '12 at 21:33

TheBoyan · Answer 3 · 2012-01-05T21:30:24.867

2

You could use this to update them by matching ids

UPDATE
    t
SET
    t.SomeNewColumn = other_table.SomeOldColumn,
FROM
    original_table t
INNER JOIN
     other_table copy
ON
    t.id = copy.id

or if you don't have the ids you might be able to pull out something by using ROW_NUMBER function to enumerate the records, but that's a long shot(I haven't checked if it's possible).

edited Jan 05 '12 at 21:30

answered Jan 05 '12 at 21:22

TheBoyan

6,802
3
45
61

score 0 · Answer 4 · answered Mar 20 '15 at 22:59

If you're updating, you'll need a primary key to join on. Usually in that case, the others' answers will suffice. If for some reason you still need to update the table with a resultset in a certain order, you can do this:

UPDATE t SET t.SomeNewColumn = copy.SomeOldColumn
FROM table t
    JOIN (SELECT ROW_NUMBER() OVER(ORDER BY id) AS row, id, SomeNewColumn FROM table) t2
        ON t2.Id = t.Id
    JOIN (SELECT ROW_NUMBER() OVER(ORDER BY id) AS row, SomeOldColumn FROM copytable) copy
        ON copy.row = t2.row

You get the new table and its row numbers in the order you want, join the old table and its row numbers in the order you want, and join back to the new table so the query has something to directly update.

How to update table based on row index?

4 Answers4