CODEWITHSUNDEEP
sqlsql-server-2008
O.O
O.O

Reputation: 11317

How to update table based on row index?

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.

Upvotes: 3

Views: 5436

Answers (4)

MikeOShay
MikeOShay

Reputation: 522

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.

Upvotes: 0

TehBoyan
TehBoyan

Reputation: 6890

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).

Upvotes: 2

Tony
Tony

Reputation: 10347

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.

Upvotes: 3

Eric
Eric

Reputation: 95203

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).

Upvotes: 3

Related Questions