Re: How to update a table using Join with other table

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 22:51:52 +0100

On Mon, 22 Mar 2004 21:39:39 +0530, Vishal Parkar wrote:

>try:
>
>begin transaction
>UPDATE a SET PHONENUM = STUFF(A.PHONENUM, 1, 3, B.NEW_NPA)
>from company A INNER JOIN phonelist B
> ON SUBSTRING(A.PHONENUM,1,3) = B.OLD_NPA
> AND SUBSTRING(A.PHONENUM,4,3) = B.PREFIX
>
>--check the data and commit/rollback accordingly

Or, if you prefer ANSI standard SQL (and avoid the sneaky errors that
the joined update may be prone to):

UPDATE company
SET PHONENUM =
   (SELECT STUFF(A.PHONENUM, 1, 3, B.NEW_NPA)
    FROM company AS A
    INNER JOIN phonelist AS b
          ON SUBSTRING(A.PHONENUM,1,3) = B.OLD_NPA
          AND SUBSTRING(A.PHONENUM,4,3) = B.PREFIX
    WHERE A.KeyColumn = company.KeyColumn)

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)