CODEWITHSUNDEEP
sqlsql-servert-sql
Prabodha Eranga
Prabodha Eranga

Reputation: 1

sql join problem

Query: 

UPDATE EMPLOYEE AS E 
INNER JOIN EMPLOYEE_TEL AS T ON E.EMP_NUMBER = T.EMP_NUMBER
   SET E.FIRST_NAME = @fname
      ,E.MID_NAME = @mname
      ,E.INITIALS =@initilas
      ,E.SURNAME = @sname
      ,E.GENDER = @gender
      ,E.CIVIL_STATUS = @CS
      ,E.DOB =@datetime 
      ,E.NIC_NUMBER = @nic
      ,E.ADDRESS_LINE1 =@adline1
      ,E.ADDRESS_LINE2 = @adline2
      ,E.ADDRESS_LINE3 = @adline3
      ,E.EMAIL = @email
      ,E.DESG_NO =@designo
      ,E.BASIC_SALARY = @sal
      ,E.TITLE = @title
      ,T.TELEPHONE=@tel
WHERE E.EMP_NUMBER=@empnum

I have tried in this SQL Server, but it came up with an error

'Msg 156, Level 15, State 1, Procedure SPUPDATEEMP, Line 21
Incorrect syntax near the keyword 'AS'.

I can't find the error. Is this wrong?

Upvotes: 0

Views: 66

Answers (3)

marc_s
marc_s

Reputation: 755321

What you're trying to do is update two tables at once - you cannot do this in SQL Server - you'll have to split this up into two separate UPDATES:

UPDATE EMPLOYEE 
SET 
  FIRST_NAME = @fname,
  MID_NAME = @mname,
  ..... 
  TITLE = @title
WHERE 
   EMP_NUMBER = @empnum

UPDATE EMPLOYEE_TEL 
SET 
   TELEPHONE = @tel
WHERE 
   EMP_NUMBER = @empnum

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135888

UPDATE EMPLLOYEE
   SET FIRST_NAME = @fname
      ,MID_NAME = @mname
      ,INITIALS =@initilas
      ,SURNAME = @sname
      ,GENDER = @gender
      ,CIVIL_STATUS = @CS
      ,DOB =@datetime 
      ,NIC_NUMBER = @nic
      ,ADDRESS_LINE1 =@adline1
      ,ADDRESS_LINE2 = @adline2
      ,ADDRESS_LINE3 = @adline3
      ,EMAIL = @email
      ,DESG_NO =@designo
      ,BASIC_SALARY = @sal
      ,TITLE = @title
WHERE EMP_NUMBER=@empnum

UPDATE EMPLOYEE_TEL
    SET TELEPHONE=@tel
    WHERE EMP_NUMBER=@empnum

Upvotes: 2

Jack Marchetti
Jack Marchetti

Reputation: 15754

When doing JOINS you don't need to use "as" to alias a table name.

However, when doing UPDATES you can't alias the name like you are trying to do.

UPDATE E
FROM Employee

Upvotes: 0

Related Questions