Query:
SELECT ID, T.c.value('@Address', 'nvarchar(20)' ) as Address
INTO #TMP
FROM TABLE1
CROSS APPLY XMLData.nodes('/Document') AS T(c)
UPDATE TABLE1
SET HomeAddress = (SELECT TOP 1 t.Address
FROM #TMP t
WHERE t.ID = ID)
Mainly, I need to copy data OUT from an XML field to normal fields within the same table.
Questions:
UPDATE T2
SET HomeAddress = t1.address
FROM TABLE2 t2
join TABLE1 t1 on T1.ID = t2.HomeAddressID
and t2.HomeAddress <> t1.address
Use a join. No need to temp table or correlated subquery.
If table 1 is in a one to many relationshisp these are some posibilites for handling that. If you havea value that indicates one and only one record (we have a field in our system that picks the most important address, it is maintained with a trigger to guarantee uniquesness), the try this:
UPDATE T2
SET HomeAddress = t1.address
FROM TABLE2 t2
join TABLE1 t1 on t1.ID = t2.HomeAddressID
WHERE t1.somefield = 'somevalue'
and t2.HomeAddress <> t1.address
If you need to based the unique record on asome other field (such as the most recent date), then try a variation of this:
UPDATE T2
SET HomeAddress = t1.address
FROM TABLE2 t2
join TABLE1 t1 on t1.ID = t2.HomeAddressID
join (select id, max(somedatefield) from table1 group by id) t3 on t3.id = t1.id
Where t2.HomeAddress <> t1.address