Update Set by Subquery

Database

Update Set by Subquery

I am very new to SQL and hope somebody can be of assistance. I have tried doing a search on the Forum for “Update Set by Subquery” but the results I found were too difficult for me to follow. Hopefully my query falls in to the category of very easy.

I have two Table A (contents Cust_num, Date_Accnt_Closed, Other_Fields) and table X (Contents Cust_num, rejection_date, Other_Fields)

In simple english, I want to copy the “rejection_date” from X so it updates Date_Accnt_Closed in table A, but only to do so where the Cust_num ’s match and there is a date in the rejection_date field to start with.

I have spent much of yesterday trying to achieve this simple objective, the code below is the last of very many tries

UPDATE
u_ukul_martdb.LB_MORE_BL_Price_Incentive

SET Date_Accnt_Closed = x.rejection_date

FROM

u_ukul_martdb.LB_MORE_BL_Price_Incentive a

INNER JOIN u_ukul_martdb.LB_MORE_BL_accepts x ON a.Cust_num = x.Cust_num

WHERE

x.rejection_date IS NOT NULL

The above error message is
3706 SYNTAX error; expecting something between the word "rejection_date" and the "FROM" keyword.

Any suggestions as to where I am going wrong. (Using Teradata sql assistant)

Bob
1 REPLY
N/A

Re: Update Set by Subquery

Use a standard ANSI update :-

UPDATE u_ukul_martdb.LB_MORE_BL_Price_Incentive a
SET Date_Accnt_Closed = u_ukul_martdb.LB_MORE_BL_accepts.rejection_date
Where
a.Cust_num = u_ukul_martdb.LB_MORE_BL_accepts.Cust_num
AND
u_ukul_martdb.LB_MORE_BL_accepts.rejection_date IS NOT NULL
;

:-)
You only need a FROM clause if there is aggregation involved or the derivation of target rows or values is complex.