Syntax error in an UPDATE statement

Database
Enthusiast

Syntax error in an UPDATE statement

I have the following UPDATE statement in which I'm getting syntax errors pointing to the line right above the FROM clause. The error from Teradata is:

Syntax error: expected something between the word 'First_name' and the 'FROM' keyword

UPDATE DATA.CONTACTS tgt
SET
tgt.LAST_NAME = DATA.PART.LAST_NAME
,tgt.BPP_USER_ID = DATA.PART.User_Id
,tgt.Email_Address = DATA.PART.Email
,tgt.Last_name = DATA.PART.Last_name
,tgt.First_name = DATA.PART.First_name
FROM
SELECT
USER_ID
, Email
,Last_name
,First_name
FROM DATA.PART
WHERE EMAIL IN (
SELECT Email_Address
FROM DATA.CONTACTS
)
qualify count(*) over (partition by email rows unbounded preceding) = 1

The error points to that first FROM clause in the statement. Anything I'm missing? Should I rearrange the clauses at all?

3 REPLIES
Enthusiast

Re: Syntax error in an UPDATE statement

UPDATE tgt 

FROM

 db.CONTACTS tgt,(SELECT  

    USER_ID

  , Email

  ,Last_name

  ,First_name

  FROM db.PART 

  WHERE  EMAIL IN (

    SELECT Email_Address

    FROM db.CONTACTS

    )

qualify count(*) over (partition by email rows unbounded preceding) = 1)a

SET

     LAST_NAME = a.LAST_NAME

    ,BPP_USER_ID = a.User_Id

    ,Email_Address = a.Email

    ,Last_name = a.Last_name

    ,First_name = a.First_name

Enthusiast

Re: Syntax error in an UPDATE statement

Thanks, looks like that got me past the syntax error.

Now, I'm getting an error saying that I don't have enough spool space. I ran the select statement separately without any errors.

Any idea as to what might be creating spool space problems in the statement?

Enthusiast

Re: Syntax error in an UPDATE statement

Spool space issue can be because of reasons like spool for a user is low, skewed data distribution .....

Did you test your explain? Are your stats fine....Which user your are using? Anything running in parallel....

You can break the logic into steps to update your target.