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.
T-SQL Implementation
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
Oracle
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