Update Set by Subquery


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


SET Date_Accnt_Closed = x.rejection_date


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


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)


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
a.Cust_num = u_ukul_martdb.LB_MORE_BL_accepts.Cust_num
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.