How to Limit the records-CodePudding

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. How can I achieve this ?

CodePudding user response：

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

CodePudding user response：

Modify using clause so that it is a subquery, and restrict number of rows in its where clause. In my example, I used the simplest option - rownum, as you didn't say which 100 rows you want.

MERGE INTO master m
     USING (SELECT *
              FROM daily d
             WHERE ROWNUM < 100) x
        ON (m.id = x.id)
WHEN MATCHED
THEN
   UPDATE SET offset = 0
WHEN NOT MATCHED
THEN
   INSERT     (id,
               col1,
               col2,
               col3,
               offset)
       VALUES (x.id,
               x.col1,
               x.col2,
               x.col3,
               NULL);