More effiect update from linked server

I am updating data from a smaller linked table into a larger table (SQL
server 2K sp4, Win server 2k3 sp1) (data which had been inserted
previously). The update now looks like:
UPDATE tableA
SET colx = (select colx FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol),
coly = (select coly FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol)
WHERE EXISTS (SELECT * FROM linkedtableB as rem
WHERE rem.pkcol = tableA.pkcol)
This seems to run an inordinately long time (in excess of 1hr + for
this one table, and I have to update 40 more tables). My question it,
what would be a more effiecient way to update this data? My reasoning
for updating this data is, while doing the insert earlier, I may have
gathered partial data during an operation, thus requiring an update at
some later point.
Any insight into a better solution would be greatly appriciated.
Daniel
danielp
try this one
UPDATE tableA SET colx =rem.colx , coly =rem.coly FROM linkedtableB rem
JOIN tableA ON
rem.pkcol = tableA.pkcol
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1162310539.241683.96330@.e3g2000cwe.googlegrou ps.com...
>I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>
|||Hi Daniel
Try
UPDATE A
SET colx = B.colx
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"danielp" wrote:

> I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>
|||I missed out a comma!
UPDATE A
SET colx = B.colx,
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi Daniel
> Try
> UPDATE A
> SET colx = B.colx
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "danielp" wrote:
|||Thanks Uri and John!
John Bell wrote:[vbcol=seagreen]
> I missed out a comma!
> UPDATE A
> SET colx = B.colx,
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "John Bell" wrote: