SQL update multi-Table Association

1. Create a test table
Create table t_user (
ID int identity primary key,
UserID varchar (50) not null,
UserName varchar (50) null,
DeptID int not null,
Phone varchar (50) null,
Fax varchar (50) null
)

Create table t_dept (
ID int identity primary key,
DeptName varchar (50) null,
Phone varchar (50) null,
Fax varchar (50) null
)

INSERT t_user
Select n '001', N 'zhang san', 1, N '000000', N '000000'
UNIONAll select n '002 ', N 'Li si', 2, N '000000', N '000000'
UNIONAll select n '003 ', N 'wang wu', 2, N '000000', N '000000'
UNIONAll select n '004 ', N 'zhao 6', 3, N '000000', N '000000'

INSERT t_dept
Select n 'dashboard ', N '123', N '123'
UNIONAll select n 'marketing Department ', N '000000', N '000000'
UNIONAll select n 'aftersales Department ', N '000000', N '000000'

Ii. SQL update (t_user data is restored after each update)

1. update t_user set u. phone = d. phone, u. fax = d. fax from t_user u, t_dept d where u. deptID = d. ID

Error: Unable to bind the identifier "u. phone" consisting of multiple parts ".
2. update u set u. phone = d. phone, u. fax = d. fax from t_user u, t_dept d where u. deptID = d. ID

 

Correct Select * from t_user    ID  UserID  UserName  DeptID  Phone  Fax  1  001  Zhang San  1  88888011  99999011  2  002  Li Si  2  88888022  99999022  3  003  Wang Wu  2  88888022  99999022  4  004  Zhao Liu  3  88888033  99999033

 

3. update t_userU set u. phone = d. phone, u. fax = d. fax from t_dept d where u. deptID = d. ID

Error: syntax error near 'u.

4. update t_user set phone = d. phone, fax = d. fax from t_dept d where deptID = d. ID

Correct. The result is the same as 2.