I have two tables, one table to be updated (TABLE1) based on the condition of a query that joins the TABLE1 and TABLE2.
for each Record on TABLE1 check if the value of COL1 exists on TABLE2 for the last 5 records from the date being analyzed on TABLE1
CREATE TABLE TABLE1 (COLDATE DATE,COL1 VARCHAR2(50), REMOVE NUMBER);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-23','12-09-20-0',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-23','23-08-19-7',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-23','11-05-19-4',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-23','06-11-20-1',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-22','12-09-20-0',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-22','23-08-19-7',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-22','11-05-19-4',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-22','06-11-20-1',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-21','12-09-20-0',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-21','23-08-19-7',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-21','11-05-19-4',7);
INSERT INTO TABLE1(COLDATE,COL1,REMOVE) VALUES ('2018-01-21','06-11-20-1',7);
CREATE TABLE TABLE2 (COLDATE DATE,COL1 VARCHAR2(50), REMOVE NUMBER);
INSERT INTO TABLE2(COLDATE,COL1) VALUES ('2018-01-23','12-09-20-0');
INSERT INTO TABLE2(COLDATE,COL1) VALUES ('2018-01-22','23-08-19-7');
INSERT INTO TABLE2(COLDATE,COL1) VALUES ('2018-01-21','11-05-19-4');
INSERT INTO TABLE2(COLDATE,COL1) VALUES ('2018-01-20','06-11-20-1');
INSERT INTO TABLE2(COLDATE,COL1) VALUES ('2018-01-19','12-09-20-0');
INSERT INTO TABLE2(COLDATE,COL1) VALUES ('2018-01-18','23-08-20-1');
INSERT INTO TABLE2(COLDATE,COL1) VALUES ('2018-01-17','11-05-19-4');
INSERT INTO TABLE2(COLDATE,COL1) VALUES ('2018-01-16','06-11-20-1');
When using the MERGE I get row locks because the table to be updated is part of the query in USING
MERGE INTO TABLE1 tbl1
USING
(
SELECT tbl1.COLDATE, tbl1.col1
FROM TABLE1 tbl1
JOIN TABLE2 tbl2
ON(tbl1.COLDATE = tbl2.COLDATE)
WHERE tbl1.col1 IN (SELECT col1 FROM TABLE2 WHERE COLDATE \< tbl2.COLDATE ORDER BY COLDATE DESC FETCH FIRST 5 ROWS ONLY)
AND tbl1.remove = 7
) Qry ON (Qry.COLDATE = tbl1.COLDATE AND Qry.col1 = tbl1.col1)
WHEN MATCHED THEN UPDATE
SET tbl1.REMOVE = 2;
When using UPDATE WHERE EXIST I get wrong results
UPDATE TABLE1 tbl1
SET REMOVE = 2
WHERE EXISTS
(
SELECT 1
FROM TABLE2 tbl2
WHERE (tbl1.COLDATE = tbl2.COLDATE)
AND tbl1.col1 IN (SELECT col1 FROM TABLE2 WHERE COLDATE \< tbl2.COLDATE ORDER BY COLDATE DESC FETCH FIRST 5 ROWS ONLY)
AND tbl1.remove = 7
);
Any suggestions how to implement this ?