Update Syntax

Database
Tourist

Update Syntax

I have alway written my update statements like the following:

UPDATE A

FROM DB.TABLEA AS A, DB.TABLEB AS B

SET NEWVALUE = B.NEWVALUE

WHERE A.ID = B.ID;

Is there any performance benefit to writing them like the following?

UPDATE DATABASENAME.TABLEA

SET NEWVALUE = DATABASENAME.TABLEB.NEWVALUE

WHERE DATABASENAME.TABLEA.ID = DATABASENAME.TABLEB.ID;

The explains are exactly the same. Just seems 2 different ways to accomplish the same results.

Thanks!

1 REPLY
Teradata Employee

Re: Update Syntax

As you note, there is no functional difference; it's just different syntax.

The first one is preferred, the second is older style, though both are Teradata extensions to ANSI standard. Teradata will also accept "correlated subquery in the SET clause" syntax:

UPDATE DATABASENAME.TABLEA AS A

SET NEWVALUE=(SELECT NEWVALUE FROM DATABASENAME.TABLEB AS B WHERE A.ID = B.ID);