Re: How do I do this update?

Allan Marcus (allan_at_est.lanl.gov) wrote:
: I could use some help with an update statement. I have two tables (A and
: B). I want to update field1 in table A with the value of field1 in table B.
: I want to join tables A and B with a field in each table (field2). Here's
: what I have so far, but this takes forever to run (Table A has 1700 rows and
: table B has 40000 rows):
 

: Update A
: set A.field1 = (select B.field1
: from B
: where a.field2 = b.field2)
: where A.field2 in (select b.field2
: from B
: where a.field2 = b.field2)
 

: In sybase this update would be easy:
 

: Update A
: set a.field1 = b.field1
: from B
: where a.field2 = b.field2
 

: Is there a better way?

If I read it right, you should be able to just say the same thing...

UPDATE A
  SET A.field1 = B.field2
  WHERE A.field2 = B.field2;

this turns A into a copy of B for those 2 fields... Hope this is what you need... otherwise please clarify

Randy

--

_/_/_/_/  _/     _/  _/      _/  _/_/_/  _/_/_/_/   _/_/_/    _/_/_/
_/        _/_/   _/   _/     _/    _/    _/    _/  _/    _/  _/    _/
_/_/_/    _/ _/  _/    _/   _/     _/    _/_/_/    _/        _/    _/
_/        _/  _/ _/     _/ _/      _/    _/        _/        _/    _/
_/_/_/_/  _/   _/_/      /_/     _/_/_/  _/         _/_/_/   _/_/_/
=====================================================================
    Randy DeWoolfson  -  ergo sum    :-)        envipco_at_cais.com
 or Ron Frederick     -  ergo ?      (8P
---------------------------------------------------------------------
#include <StandardDisclaimerOnOpinions>  // Null if file not found.
=====================================================================