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.
Danieldanielp
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.googlegroups.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:
> 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
> >
> >|||Thanks Uri and John!
John Bell wrote:
> 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:
> > 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
> > >
> > >sql