Teradata Update Table from Select Statement
Sorry if the title is unclear. Basically I'm trying to select certain records from multiple tables then update a certain column value for the returned records.
UPDATE CUSTOMERS SET LIKES_US = 'Y' FROM RESTAURANT REST INNER JOIN CUSTOMERS CUST ON REST.LINK_ID = CUST.LINK_ID WHERE REST.REST_TYPE = 'Diner' AND CUST.LIKES_US IS NULL
UPDATE (SELECT CUST.LIKES_US FROM CUSTOMERS CUST INNER JOIN RESTAURANT REST ON CUST.LINK_ID=REST.LINK_ID WHERE REST.REST_TYPE = 'Diner' AND CUST.LIKES_US IS NULL) NEW_CUST SET NEW_CUST.LIKES_US = 'Y';
I am tried doing the same thing in Teradata as I did in Oracle but I get the following error:
Executed as Single statement. Failed [3707 : 42000] Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between the 'UPDATE' keyword and '('. Elapsed time = 00:00:00.003 STATEMENT 1: Unknown failed.
I looked online for the solution but had no luck.
Have you tried the following syntax with Teradata:
UPDATE CUSTOMERS C1 FROM (SELECT C2.LINK_ID FROM CUSTOMERS C2 INNER JOIN RESTAURANTS R2 ON C2.LINK_ID = R2.LINK_ID WHERE R2.REST_TYPE = 'DINER' AND C2.LIKES_US IS NULL) D1 SET LIKES_US = 'Y' WHERE C1.LINK_ID = C2.LINK_ID
I think that in this specific case, the below query will perform a little better since it needs one less join.
UPDATE C FROM CUSTOMERS C, RESTAURANTS R SET LIKES_US = 'Y' WHERE C.LINK_ID = R.LINK_ID AND R.REST_TYPE = 'DINER' AND C.LIKES_US IS NULL