Update from Temp Table

Question

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:

Any reason why all the records get the HomeAddress on Table1?
Is really Cursor the only way to update the value on Table1?

possible duplicate of [Update a table using JOIN in SQL Server?](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) — Aaron Bertrand, Feb 27 '13 at 18:06

HLGEM · Answer 1 · 2013-02-27T18:07:48.187

24

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

edited Feb 27 '13 at 18:07

answered Feb 27 '13 at 17:53

HLGEM

94,695
15
113
186

2

If t1.id has a one to many relationship with t2.homeaddressid, you might need to be a bit more specific on what record to use. – Dan Bracuk Feb 27 '13 at 17:55
the real values are inside an XML field. I just updated the original question. – SF Developer Feb 27 '13 at 17:57
@DanBracuk, excellent point. Of course the current version isn't specifying which to use either in that case. Let me cobble up some examples though. – HLGEM Feb 27 '13 at 17:57

score 4 · Answer 2 · edited Apr 30 '20 at 15:29

4

On the update, I need to FULLY QUALIFY the Table as follow:

UPDATE TABLE1 SET TABLE1.HomeAddress = (SELECT TOP 1 t.Address  
               FROM #TMP t  
               WHERE t.ID = TABLE1.ID)

edited Apr 30 '20 at 15:29

Mohan Singh

1,142
3
15
30

answered Feb 27 '13 at 18:02

SF Developer

5,244
14
60
106

score 0 · Answer 3 · edited Jan 04 '19 at 00:41

SELECT P.TipoComprob,P.NoComprob,C.Importe as ImpIVA1,ROUND(100/P.ImpGravado*C.Importe,1)
 as PorcIVA1 
       INTO #Temporal
       FROM ComprobProv AS P
       LEFT JOIN PasesCompras AS C ON C.TipoComprob=P.TipoAsiento AND 
C.NoComprob=P.NoComprob
WHERE P.PorcIVA1 =0 and P.CatIVA = 'Ri' AND P.ImpGravado>0 AND C.CodCuenta=110303010 AND ROUND(100/P.ImpGravado*C.Importe,1) IN (21.00,10.50,27.00);
go

select * from #Temporal;
go

UPDATE 
   t1 
SET 
   t1.ImpIVA1 =  t2.ImpIVA1, t1.PorcIVA1 = t2.PorcIVA1
FROM
   dbo.ComprobProv t1
   INNER JOIN #Temporal t2
   ON t1.TipoComprob = t2.TipoComprob AND t1.NoComprob = t2.NoComprob;
go

-- Note that the 'GO' are important to preserve the context in SQL Server 2017, otherwise you'll find an 'unknown field name' error.

Update from Temp Table

3 Answers3