CODEWITHSUNDEEP
sqlteradata
Pepe
Pepe

Reputation: 6480

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.

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.

Upvotes: 3

Views: 64107

Answers (3)

access_granted
access_granted

Reputation: 1927

@Rob Paller

I don't think we can use this syntax (breaks in TD17)

This one works.

UPDATE CUSTOMERS
  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 CUSTOMERS.LINK_ID = D1.LINK_ID

Upvotes: 0

jacobjonz
jacobjonz

Reputation: 126

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

Upvotes: 3

Rob Paller
Rob Paller

Reputation: 7786

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

Upvotes: 10

Related Questions