Search code examples
sqlsql-updateddl

Update a column in table which has a temp id with real id from the same column

I have come across a unique situation where I have a column called id which may have temp id until the final id comes through like:

id temp id
1 null
2 1
6 null
7 6

I want a query that updates the table as :

id temp id
2 null
2 1
7 null
7 6

basically once the id has a temp id associated with id, we just update all those temp ids with the real_id.

Any idea if this can be achieved. I try using case statements inside the updated table set but this doesn't work for me and also there are thousands of such records.

No issues with the temp id being redundant later because that id cannot repeat itself and thus it will not be a concern for analysis as we will use id only for analysis

Solution

  • You can use an update:

    update t
    set id = (select t2.id from t t2 where t2.tempid = t.id)
    where t.tempid is null;