MERGE INTO

Database

MERGE INTO

Hi all,

My current employer is using an Oracle database, but is migrating to Teradata.
I've been experimenting with converting Oracle SQL queries to Teradata SQL and failing miserably.

Can anyone produce an example MERGE INTO query that works.
I've been reading user guides, searching the forums and havent seen any examples that I've been able to replicate successfully.

I've seen so many different errors, but this one has to be my favourite:
5760: The SELECT subquery from the table reference must fully specify either a UPI value or else a USI value.

--Example of the code Im trying to run
MERGE INTO
OriginalTable AS t1
USING
(SELECT Field1 FROM NewTable) AS t2
ON
(t1.Field1 = t2.Field1)
WHEN MATCHED THEN
UPDATE SET
t1.Field2 = 'Merge'
WHEN NOT MATCHED THEN
INSERT
(t1.Field1, t1.Field2)
VALUES
(t2.Field1, 'New');

The table structures are as follows:
OriginalTable
Field1 varchar(100)
Primary Key: Field1

NewTable
Field1 varchar(100),
Field2 varchar(50)
Primary Key: Field1

Can anyone offer any advice.....???

3 REPLIES
Enthusiast

Re: MERGE INTO

Which Teradata version are you using? When I tried your SQL on my version 13.0 system, I got a syntax error, " Failure 3810 Column/Parameter 'JBC.t1.t1' does not exist."

It seems the Teradata syntax does not allow the target column qualifers. The correct SQL, below, works on my system:

MERGE INTO
OriginalTable AS t1
USING
(SELECT Field1 FROM NewTable) AS t2
ON
(t1.Field1 = t2.Field1)
WHEN MATCHED THEN
UPDATE SET
Field2 = 'Merge'
WHEN NOT MATCHED THEN
INSERT
(Field1, Field2)
VALUES
(t2.Field1, 'New');

Re: MERGE INTO

Thanks for replying,

Unfortunately, that code still doesnt work and Im getting that familiar error message:
5760: The SELECT subquery from the table reference must fully specify either a UPI value or else a USI value.

Not sure exactly what version I am using, I will investigate.
According the Sql Assist (v7.2) the driver is v3.06 and the server is v6.02 if that helps.
Im assuming that the server version might be the answer, but I didnt have any involvement in setting up the system.

The exact code Im running is:

-----------------------------------------

CREATE SET TABLE OriginalTable
(
Field1 VARCHAR(20) NOT NULL
)
PRIMARY INDEX (Field1);


CREATE SET TABLE NewTable
(
Field1 VARCHAR(20) NOT NULL,
Field2 VARCHAR(20) NOT NULL
)
PRIMARY INDEX (Field1);


INSERT INTO OriginalTable
(Field1)
VALUES ('Test1');

INSERT INTO OriginalTable
(Field1)
VALUES ('Test2');

INSERT INTO OriginalTable
(Field1)
VALUES ('Test3');


MERGE INTO
NewTable AS t1
USING
(SELECT AND CONSUME Field1 FROM OriginalTable) AS t2
ON
(t1.Field1 = t2.Field1)
WHEN MATCHED THEN
UPDATE SET
Field2 = 'Merge'
WHEN NOT MATCHED THEN
INSERT (Field1, Field2)
VALUES (t2.Field1, 'New');


Enthusiast

Re: MERGE INTO

The full ANSI/ISO SQL standard MERGE statement was not supported until Teradata version 12.0.