MERGE-INO

Database
Enthusiast

MERGE-INO

I am trying to convert the below INSERT and UPDATE statements in to one merge-in SQL so that I can save some CPU and TIME but nothing is working as expected. Any help is greatly appreciated.

Note: Column (B,E) are the primary index for the TB_S in both DB_X and DB_Y.

INSERT INTO DB_X.TBL_S

(

A,

B,

C,

D,

E,

F,

G,

H)

Select

A,

B,

C,

D,

E,

F,

G,

H

From

(

SELECT

G_T1.B as B,

G_T1.A as A, 

G_T1.C as C ,

G_T1.D as D ,

G_T1.E as E,

G_T1.F as F,

G_T1.G as G,

G_T1.H as H ,

CASE WHEN G_T0.B IS NULL THEN 'I' 

ELSE 'U' 

END as  STATUS_IND, 

CASE WHEN G_T0.B IS NULL THEN 'I' 

ELSE 'U' 

END as  STATUS 

FROM DB_X.TBL_S G_T0 RIGHT OUTER JOIN DB_Y.TBL_S G_T1

ON G_T1.A = G_T0.A 

AND G_T1.B = G_T0.B 

AND G_T1.C = G_T0.C

WHERE G_T1.A  = 'ZZ' 

and STATUS = 'I')A;

UPDATE DB_X.TBL_S

FROM

(

SELECT

G_T1.B as B,

G_T1.A as A,

G_T1.C as C ,

G_T1.D as D ,

G_T1.E as E,

G_T1.F as F,

G_T1.G as G,

G_T1.H as H ,

CASE WHEN G_T0.B IS NULL THEN 'I' 

ELSE 'U' 

END as  STATUS_IND, 

CASE WHEN G_T0.B IS NULL THEN 'I' 

ELSE 'U' 

END as  STATUS 

FROM DB_X.TBL_S G_T0 RIGHT OUTER JOIN DB_Y.TBL_S G_T1

ON G_T1.A = G_T0.A 

AND G_T1.B = G_T0.B 

AND G_T1.C = G_T0.C

WHERE G_T1.A  = 'ZZ' 

and STATUS = 'U')A

SET

D = A.D,

E = A.E,

F = A.F,

G = A.G,

H = A.H

where

DB_X.TBL_S.A = A.A 

and

DB_X.TBL_S.C = A.C 

and

DB_X.TBL_S.B = A.B

;

______________________

I am currently stuck at this:

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

MERGE INTO DB_X.TBL_S as G_T0

USING (

SELECT

a.A , 

a.B , 

a.C  , 

a.D  , 

a.E , 

a.F , 

a.G , 

a.H 

FROM DB_Y.TBL_S a 

 left outer join DB_X.TBL_S b

 on A.B = B.B 

AND A.A = B.A 

AND A.C = B.C  ) as G_T1 

 ON (G_T1.B  = 'ZZ' 

AND G_T1.A IS NOT NULL 

AND

/*I've included the join on PI columns (B,E) other wise I am getting the following error

5758 The search condition must fully specify the

Target table primary index and partition

column(s) and expression must match

INSERT specification primary index and

partition column(s).

*/


G_T1.E = G_T0.E 

AND

G_T1.B = G_T0.B 

AND

G_T1.A = G_T0.A )

WHEN MATCHED THEN 

UPDATE

SET

D = G_T1.D, 

E = G_T1.E, 

F = G_T1.F, 

G = G_T1.G, 

H = G_T1.H 

WHEN NOT MATCHED THEN 

INSERT (

A,

B,

C,

D,

E,

F,

G,

H)

VALUES ( G_T1.A , 

G_T1.B ,  

G_T1.C  , 

G_T1.D  , 

G_T1.E , 

G_T1.F , 

G_T1.G , 

G_T1.H );

The error with the above MERGE-IN SQL is:


EXPLAIN Failed.  [5977] Invalid MERGE-INTO statement: Update of Primary index or partition column is not allowed.

2 REPLIES
Enthusiast

Re: MERGE-INO

The error message is pretty clear that you have PI or Partition Column included in your SQL statement, you need to remove those columns. As you haven't provided the ddl so its difficult to suggest which columns you need to take out.

Share the DDL of the tables included, may be we can pin point the issue!

Enthusiast

Re: MERGE-INO

Just remove the PI or partition column from below statement and it should work.

SET

D = G_T1.D, 

E = G_T1.E, 

F = G_T1.F, 

G = G_T1.G, 

H = G_T1.H