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
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)
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.