CODEWITHSUNDEEP
sqloracle-database
Chopper Lee
Chopper Lee

Reputation: 1577

how to only update a single row when matches multiple row in ‘Merge into’ statement

update table set column1 = value where column2 = match_value and rownum =1

it can only update a single row when matches multiple row , but how to only update a single row when matches multiple row in ‘Merge into’ statement;

    merge into table1
using table2
on (table1.column1 = table2.column1)
when mateched then
update
set column2 = table2.column2;

Upvotes: 0

Views: 1686

Answers (1)

Mikhail
Mikhail

Reputation: 1560

This worked for me:

merge into table1
using table2
on (table1.column1 = table2.column1 and rownum = 1)
when mateched then
update
set column2 = table2.column2;

For example (Oracle 11gR2):

CREATE TABLE TST_MERGE
       (ID NUMBER,
       VAL NUMBER);

INSERT INTO TST_MERGE (ID, VAL)
VALUES (1,1);
INSERT INTO TST_MERGE (ID, VAL)
VALUES (1,2);
INSERT INTO TST_MERGE (ID, VAL)
VALUES (2,2);
INSERT INTO TST_MERGE (ID, VAL)
VALUES (2,3);

CREATE TABLE TST_MERGE1
       (ID NUMBER,
       VAL NUMBER);

INSERT INTO TST_MERGE1 (ID, VAL)
VALUES (1,-1);
INSERT INTO TST_MERGE1 (ID, VAL)
VALUES (2,-1);

MERGE INTO TST_MERGE a
USING (SELECT * FROM TST_MERGE1
WHERE ID = 1) b
ON (a.id = b.id AND rownum = 1)
WHEN MATCHED THEN UPDATE
SET a.val = b.val;

MERGE INTO TST_MERGE a
USING (SELECT * FROM TST_MERGE1
WHERE ID = 2) b
ON (a.id = b.id AND rownum = 1)
WHEN MATCHED THEN UPDATE
SET a.val = b.val;

SELECT * FROM TST_MERGE;

ID      VAL
1   -1
1   2
2   -1
2   3

Upvotes: 2

Related Questions