I'm needing to produce a set of missing records in oracle-CodePudding

I have a source table and a branch table.

I need to update the sequence by 1 starting with the max value and populate a branch number not already being used. My results should be Seqno starting at 1409 with a new branch number not in the source table already.

Here's what I have so far. I get all values 1409, but they need to be 1410, 1411,... and so on

SELECT  S.SOURCEID
    ,  MAX(S.SEQNO)   1 AS NEWSEQ
    , B.BRANCH_NO 
     FROM SOURCE S, BRANCH B
   WHERE S.SOURCEID = '607'
   AND B.BRANCH <> S.BRANCH
    GROUP BY S.SOURCEID,B.BRANCH,S.SEQNO

CodePudding user response：

Don't use MAX(seqno) 1 to generate the values (you will end up with duplicates if you run the query on a parallel system). Instead, create a sequence:

CREATE SEQUENCE source__seqno START WITH 1409;

Then you can use:

INSERT INTO source (sourceid, seqno, branch)
SELECT S.SOURCEID
     , source__seqno.NEXTVAL
     , B.BRANCH
FROM   SOURCE S
       INNER JOIN BRANCH B
       ON (B.BRANCH <> S.BRANCH)
WHERE  S.SOURCEID = '607'

db<>fiddle here

CodePudding user response：

You need to use ROWNUM instead of 1.

SELECT S.SOURCEID
      ,(SELECT MAX(SEQNO) FROM SOURCE)   ROWNUM AS NEWSEQ
      ,B.BRANCH_NO 
  FROM SOURCE S
  JOIN BRANCH B ON B.BRANCH <> SB.BRANCH
 WHERE S.SOURCEID = '607'
 GROUP BY S.SOURCEID,B.BRANCH

Using only the MAX condition will give the max from the query not from the whole table. So I have used a sub-query for that. Also, I have changed your join to modern syntax.

P.S. - Not sure why you have used <> condition instead of the equality condition. Normally we use equality condition for joins.