Martin Hatch Martin Hatch - 1 year ago 36
SQL Question

Update table columns based on query to the same table

This has stumped me a bit, and I'm not a SQL guru so please bear with me.

I have a table where each row can have a "parent" (to form a hierarchy). e.g. "Item 1.1" has "Item 1" as it's parent.

The ID column is changing (in this example, from alphanumeric to auto-incrementing numbers).

How do I populate the new "ParentId" field .. by working out the correct "parent" from the existing column data?

I have a (very simplified) table as follows:

OldId OldParentId Title NewId NewParentId
A Null Item 1 1 Null
B A Item 1.1 2 Null
C A Item 1.2 3 Null
D Null Item 2 4 Null
E D Item 2.1 5 Null

What I am trying to do is populate the "NewParentId" field, based on a lookup against the existing table.

My (clearly not working) pseudo-code is:

Update ExampleTable
Set NewParentId = (Select Top 1 NewId From ExampleTable WHERE OldId = ???.OldParentId)

I don't know how to reference the "current row being updated" in the select query.

Is this even the correct approach to achieve this?

Oh - we also need to appropriately identify and handle "nulls" (e.g. "Item 1" and "Item 2" don't have a Parent .. so the Parent ID values should retain their "Null" value)

Answer Source

You can do it like:

update T
set NewParentId = T1.NewId
from ExampleTable as T
   outer apply (select top 1 T1.NewId 
                from ExampleTable as T1 
                where T1.OldId = T.OldParentId) as T1