Update and or insert

[Privesh Baliper]

Hi

I have two tables with the same structur: called DISPLAYLOAYOUT and DISPLAYLAOUT1 :

CREATE TABLE CARDBA.DISPLAYL AYOUT - DL
(
AREACODE VARCHAR2(5 BYTE) NOT NULL,
ZONECODE VARCHAR2(5 BYTE) NOT NULL,
LOCATIONCODE VARCHAR2(5 BYTE) NOT NULL,
RECTORIGINX NUMBER,
RECTORIGINY NUMBER,
RECTWIDTH NUMBER,
RECTHEIGHT NUMBER,
RECTDESTX NUMBER,
RECTDESTY NUMBER,
TXTORIGINX NUMBER,
TXTORIGINY NUMBER,
TXTWIDTH NUMBER,
TXTHEIGHT NUMBER,
TXTDESTX NUMBER,
TXTDESTY NUMBER
)
CREATE TABLE CARDBA.DISPLAYL AYOUT1 - DL1
(
AREACODE VARCHAR2(5 BYTE) NOT NULL,
ZONECODE VARCHAR2(5 BYTE) NOT NULL,
LOCATIONCODE VARCHAR2(5 BYTE) NOT NULL,
RECTORIGINX NUMBER,
RECTORIGINY NUMBER,
RECTWIDTH NUMBER,
RECTHEIGHT NUMBER,
RECTDESTX NUMBER,
RECTDESTY NUMBER,
TXTORIGINX NUMBER,
TXTORIGINY NUMBER,
TXTWIDTH NUMBER,
TXTHEIGHT NUMBER,
TXTDESTX NUMBER,
TXTDESTY NUMBER
)

AREACODE, ZONECODE, LOCATIONCODE make up the primary key

I'm new to sql, what I would like to do is this:

match DL1 with DL with the pk

if no row exists then insert row from DL1 into DL
if the row exists then update all other rect and txt fields (all other fields) in DL with those values in DL1

Can anyone help me? Your help would be much appreciated

Thanks in advance

Privs

[amitpatel66]

MERGE STATEMENT is what you need to use.

Check More
Another one

[Privesh Baliper]

Hi

I'm using this:

MERGE INTO CARDBA.DISPLAYL AYOUT A
USING (SELECT AREACODE,
ZONECODE,
LOCATIONCODE,
RECTDESTX,
RECTDESTY,
RECTHEIGHT,
RECTORIGINX,
RECTORIGINY,
RECTWIDTH,
TXTDESTX,
TXTDESTY,
TXTHEIGHT,
TXTORIGINX,
TXTORIGINY,
TXTWIDTH
FROM CARDBA.DISPLAYL AYOUT1) b
ON (A.AREACODE = B.AREACODE
AND A.ZONECODE = B.ZONECODE
AND A.LOCATIONCODE = B.LOCATIONCODE)
WHEN NOT MATCHED
THEN
INSERT (AREACODE,
ZONECODE,
LOCATIONCODE,
RECTDESTX,
RECTDESTY,
RECTHEIGHT,
RECTORIGINX,
RECTORIGINY,
RECTWIDTH,
TXTDESTX,
TXTDESTY,
TXTHEIGHT,
TXTORIGINX,
TXTORIGINY,
TXTWIDTH)
VALUES (b.AREACODE,
b.ZONECODE,
b.LOCATIONCODE,
b.RECTDESTX,
b.RECTDESTY,
b.RECTHEIGHT,
b.RECTORIGINX,
b.RECTORIGINY,
b.RECTWIDTH,
b.TXTDESTX,
b.TXTDESTY,
b.TXTHEIGHT,
b.TXTORIGINX,
b.TXTORIGINY,
b.TXTWIDTH)
WHEN MATCHED
THEN
UPDATE SET a.RECTDESTX = b.RECTDESTX,
A.RECTDESTY = B.RECTDESTY,
A.RECTHEIGHT = b.RECTHEIGHT,
A.RECTORIGINX = b.RECTORIGINX,
A.RECTORIGINY = b.RECTORIGINY,
A.RECTWIDTH = b.RECTWIDTH,
A.TXTDESTX = b.TXTDESTX,
A.TXTDESTY = b.TXTDESTY,
A.TXTHEIGHT = b.TXTHEIGHT,
A.TXTORIGINX = b.TXTORIGINX,
A.TXTORIGINY = b.TXTORIGINY,
A.TXTWIDTH = b.TXTWIDTH;

but i get this error: Error at line 2
ORA-30926: unable to get a stable set of rows in the source tables

[amitpatel66]

It looks like your source table returns more than one row matched to your target table. You will need to make sure that your where clause contain all the necessary joins in order to do the merge operation.

[Privesh Baliper]

thanks, I seem to have removed the duplicate rows and it worked

Thank you sooo much

[amitpatel66]

You are welcome.....