How to Limit the records

Question

I have two tables called daily and master. Daily table is truncated every day but Master table holds the data and is never truncated. Every day I run a SQL script to merge the Daily table data with Master table as below inside a stored procedure:

UPDATE master
SET offset = COALESCE(offset + 1, 0);

MERGE INTO master m
USING daily d
ON (m.id = d.id)
WHEN MATCHED THEN
  UPDATE SET offset = 0
WHEN NOT MATCHED THEN
  INSERT (id, col1, col2, col3, offset)
  VALUES (d.id, d.col1, d.col2, d.col3, NULL);

This works fine but in the WHEN NOT MATCHED clause, I need to Insert only a maximum of 100 records from Daily to Master. No there is no sorting criteria for the data to be inserted. How can I achieve this ?

I'd break that out of the `MERGE` and into separate `UPDATE` and `INSERT` statements. It becomes trivial at that point. — Eric Brandt, Nov 09 '21 at 14:06
Is there any sorting criteria for the data to be inserted(or arbitrarily), and What's the version of the DB? — Barbaros Özhan, Nov 09 '21 at 14:26
@EricBrandt: please can you help what the seperate UPDATE and INSERT statement would look like ? — tanatan, Nov 10 '21 at 09:03
@BarbarosÖzhan: No there is no sorting criteria. Oracle version is 12.x — tanatan, Nov 10 '21 at 09:03

score 0 · Answer 1 · answered Nov 09 '21 at 14:23

You could use:

MERGE INTO master m
USING (
  SELECT *
  FROM   (
    SELECT d.*,
           m.ROWID AS rid,
           ROW_NUMBER() OVER (
             PARTITION BY CASE WHEN m.ROWID IS NULL
                          THEN 'Insert'
                          ELSE 'Update'
                          END
             ORDER     BY ROWNUM
           ) AS rn
    FROM   daily d
           LEFT OUTER JOIN master m
           ON (m.id = d.id)
  )
  WHERE  rid IS NOT NULL -- Update all rows.
  OR     rn <= 100       -- Only insert 100 rows.
) d
ON (m.ROWID = d.rid)     -- Use the ROWID psuedo-column to join.
WHEN MATCHED THEN
  UPDATE SET offset = 0
WHEN NOT MATCHED THEN
  INSERT (id, col1, col2, col3, offset)
  VALUES (d.id, d.col1, d.col2, d.col3, NULL);

db<>fiddle here

score 0 · Answer 2 · answered Nov 10 '21 at 11:39

The main idea is to check the existence of the new records,which come from daily, whether exist within the table master or not as fetching all records from master while restricting them to 100 rows for the data coming from master.

use NVL2() function to determine the existence
filter non-existent data by using FETCH clause for 100 rows as using the database with version 12 without using an ORDER BY clause since you don't need any sorting criteria while fetching data.

So, replace the table name daily in your MERGE statement with the following subquery

(
 WITH t AS
 (
  SELECT d.*, NVL2(m.id,1,0) AS exist
    FROM daily d
    LEFT JOIN master m
      ON m.id = d.id 
 )
 SELECT *
   FROM t
  WHERE exist = 1
  UNION ALL
(SELECT *
   FROM t
  WHERE exist = 0
  FETCH FIRST 100 ROWS ONLY)
)

Thank you @MT0 for the Demo

How to Limit the records

2 Answers2