SQL-Server 2014. UPDATE joining two tables and using table aliases

Question

I have two almost identical tables. Need to update every Postal in TABLE_03 when it's NULL There's no ID or Primary Key to use so I'm linking these with columns Link, Name and PhoneNum / ( Phone in TABLE_ALL )

Tried SQL:

 UPDATE TABLE_03 a
   join TABLE_ALL b 
   ON   a.Link = b.Link,
        a.Name = b.Name,
        a.PhoneNum = b.Phone
   SET a.Postal = b.PostalCode
  WHERE a.Postal IS NULL;

SQL Server gives error

Incorrect syntax near 'a'.

Without aliases :

  UPDATE TABLE_03 
   join TABLE_ALL 
   ON   TABLE_03.Link = TABLE_ALL.Link,
        TABLE_03.Name = TABLE_ALL.Name,
        TABLE_03.PhoneNum = TABLE_ALL.Phone
   SET TABLE_03.Postal = TABLE_ALL.PostalCode
  WHERE TABLE_03.Postal IS NULL;

Gives error:

Incorrect syntax near the keyword 'join'.

please check this http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 — iwan, Sep 11 '15 at 09:05
possible duplicate of [Update with two tables?](http://stackoverflow.com/questions/1068447/update-with-two-tables) — A ツ, Sep 11 '15 at 09:09

score 3 · Accepted Answer · answered Sep 11 '15 at 09:06

Your syntax is wrong, mainly the JOIN ON clause. It should be like below using AND operator

UPDATE TABLE_03 a
   join TABLE_ALL b 
   ON   a.Link = b.Link AND
        a.Name = b.Name AND
        a.PhoneNum = b.Phone
   SET a.Postal = b.PostalCode
  WHERE a.Postal IS NULL;

With proper SQL Server syntax

UPDATE a
SET a.Postal = b.PostalCode
FROM TABLE_03 a
   join TABLE_ALL b 
   ON   a.Link = b.Link AND
        a.Name = b.Name AND
        a.PhoneNum = b.Phone   
  WHERE a.Postal IS NULL;

score 0 · Answer 2 · answered Sep 11 '15 at 09:04

0

try this

       UPDATE TABLE_03 
 SET TABLE_03.Postal = TABLE_ALL.PostalCode
 from TABLE_ALL 
 where  TABLE_03.Link = TABLE_ALL.Link AND 
    TABLE_03.Name = TABLE_ALL.NAME AND 
    TABLE_03.PhoneNum = TABLE_ALL.Phone and

   TABLE_03.Postal IS NULL;

answered Sep 11 '15 at 09:04

Jayanti Lal

1,175
6
18

score 0 · Answer 3 · answered Sep 11 '15 at 09:06

0

Try this:

 UPDATE a
   SET a.Postal = b.PostalCode
   FROM TABLE_03 a
   join TABLE_ALL b 
   ON   a.Link = b.Link,
        a.Name = b.Name,
        a.PhoneNum = b.Phone
  WHERE a.Postal IS NULL;

answered Sep 11 '15 at 09:06

Tom

747
5
16

SQL-Server 2014. UPDATE joining two tables and using table aliases

3 Answers3