Why does Hyper-Q employ emulation for simple UPDATE statements with join on SQL DW?

Datometry Hyper-Q implements the semantics of the source system on the target system. Operations that exist natively on the target system may differ in semantics, requiring emulation to achieve semantically identical behavior.

Teradata UPDATE semantics differ from SQL DW UPDATE semantics when multiple source rows update the same target row. On Teradata, this results in an error, failing the UPDATE statement. In SQL DW, this UPDATE goes through and non-deterministically updates the target row with any of the source rows.

To provide Teradata semantics on SQL DW and error out when multiple source rows update the same target row, Hyper-Q generally emulates UPDATE statements on SQL DW. This emulation can be disabled upon request to pass through simple UPDATE statements directly to SQL DW, implying SQL DW semantics when multiple source rows update the same target row.

For additional information, please refer to the example below and to this article on complex UPDATE statements.

Example

Consider the following two tables:

CREATE TABLE employees (empid INTEGER, empname VARCHAR(100), empphone VARCHAR(20));
CREATE TABLE empdepts (empid INTEGER, depname VARCHAR(100), empphone VARCHAR(20));

INSERT INTO employees VALUES (1, 'Francine Smith', NULL);

INSERT INTO empdepts VALUES (1, 'Marketing', '555-111-1111');
INSERT INTO empdepts VALUES (1, 'Engineering', '555-333-3333');

When updating the employee's phone number in table employees from the table empdepts by joining on empid, two source rows from empdepts are candidates to update the same target row in employees.

On Teradata, this operation fails:

UPDATE E
FROM employees E, empdepts D
SET empphone = D.empphone
WHERE E.empid = D.empid;

[Teradata Database] [TeraJDBC 15.10.00.37] [Error 7547] [SQLState HY000] Target row updated by multiple source rows. [SQL State=HY000, DB Errorcode=7547]

On SQL DW, this operation succeeds:

UPDATE employees
SET empphone = D.empphone
FROM empdepts D
WHERE employees.empid = D.empid;

UPDATE employees successful
1 row affected

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.