CODEWITHSUNDEEP
sqlinner-join
Riva
Riva

Reputation: 1

SQL Inner Join w Update

I have two tables. I am trying to update the state code in the first wherever it is incorrect (>2 characters)by linking it to another table via the zip code.

I keep getting errors in my code that the join is not properly ended - I have been staring at it for over two hours - it seems so straightforward but I just can't see the mistake?

Table Name – ATEST
ZIP     CITY              ST1
19090   WILLOW GROVE       PA
19091   MEDIA              PA
19092   PHILADELPHIA       PA
19093   PHILATEST          PENN
19094   WOODLYN            PA
95123   SAN JOSE           CA
95124   SAN JOSETEST       CAA

Table Name – AZIP_INFO
ZIP     CITY            ST
19090   WILLOW GROVE    PA
19091   MEDIA           PA
19092   PHILADELPHIA    PA
19093   PHILATEST       PA
19094   WOODLYN         PA
95123   SAN JOSE        CA
95124   SAN JOSETEST    CA


UPDATE  /*+ parallel(2) */ ATEST
SET (ATEST.ST1) = (AZIP_INFO.ST)
INNER JOIN (ATEST.ZIP) = AZIP_INFO.ZIP)
ON (TRIM (ATEST.ZIP) = AZIP_INFO.ZIP)
WHERE LENGTH(ATEST.ST1) > 2

Thanks in advance for any help you can give!

Upvotes: 0

Views: 34

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

Your hint just reeks of Oracle, which does not support JOINs in UPDATEs (for most purposes).

You can do:

UPDATE ATEST a
    SET a.ST1 = (SELECT ai.ST
                 FROM AZIP_INFOR ai
                 WHERE a.ZIP = ai.ZIP
                )
    WHERE EXISTS (SELECT 1
                  FROM AZIP_INFOR ai
                  WHERE a.ZIP = ai.ZIP
                 )
    WHERE LENGTH(a.ST1) > 2;

Upvotes: 1

AswinRajaram
AswinRajaram

Reputation: 1632

Can you try this?

UPDATE  ATEST
SET ATEST.ST1 =  AZIP_INFO.ST
FROM ATEST
INNER JOIN AZIP 
ON TRIM(ATEST.ZIP) = AZIP_INFO.ZIP
WHERE LENGTH(ATEST.ST1) > 2

Upvotes: 1

Related Questions