Forum Stats

  • 3,839,092 Users
  • 2,262,450 Discussions
  • 7,900,854 Comments

Discussions

Query to find out rows deleted from a table

ORA_AJ
ORA_AJ Member Posts: 2,295
edited Oct 19, 2010 2:30AM in SQL & PL/SQL
Hi

Suppose i have a table with a sequence i.e its an header table containing GRN nos.Now suppose someone has deleted some of serials from it like i am having 1,2,4,5,6 etc 3 is missing.Now what will be the query to find out what are the serials that are missed out in the table.


--Ajay Sharma

Answers

  • MaheshKaila
    MaheshKaila Member Posts: 310
    Try this one
    WITH test AS (SELECT '1' id
                  FROM DUAL
                  UNION ALL
                  SELECT '2' id
                  FROM DUAL
                  UNION ALL
                  SELECT '4' id
                  FROM DUAL
                  UNION ALL
                  SELECT '5' id
                  FROM DUAL
                  UNION ALL
                  SELECT '6' id
                  FROM DUAL)
    SELECT TO_CHAR (l)
    FROM (SELECT LEVEL l
          FROM DUAL
          CONNECT BY LEVEL <= (SELECT MAX (id)
                               FROM test))
    MINUS
    SELECT id
    FROM test 
    Regards,
    Mahesh Kaila
  • ORA_AJ
    ORA_AJ Member Posts: 2,295
    Dear Mahesh


    In my case Number of records is not fixed i mean to say its not neccesary that data is there for 1-6 only.It can be upto 100..


    --Ajay Sharma
  • MaheshKaila
    MaheshKaila Member Posts: 310
    SELECT TO_CHAR (l)
    FROM (SELECT LEVEL l
          FROM DUAL
          CONNECT BY LEVEL <= (SELECT MAX (id)
                               FROM <YOUR_TABLE>))
    MINUS
    SELECT id
    FROM <YOUR_TABLE>
    - Mahesh Kaila
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    What if i delete 4,5 and 6 will your query find it?
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I like recursive with clause B-)
    Hehe I used PostgreSQL8.4 because I do not have Oracle11gR2 :D
    WITH recursive t(Val) AS (
    values(1),(2),(4),(5),(6),(12)),
    rec(Val,LeadVal) as(
    select Val+1,LeadVal
      from (select Val,Lead(Val) over(order by Val) as LeadVal
              from t) tmp
     where Val+1 < LeadVal
    union all
    select Val+1,LeadVal
      from rec
     where Val+1 < LeadVal)
    select Val from rec;
    
     val
    -----
       3
       7
       8
       9
      10
      11
  • MaheshKaila
    MaheshKaila Member Posts: 310
    WITH test AS (SELECT '1' id
                  FROM DUAL
                  UNION ALL
                  SELECT '2' id
                  FROM DUAL
                  UNION ALL
                  SELECT '4' id
                  FROM DUAL
                  UNION ALL
                  SELECT '5' id
                  FROM DUAL
                  UNION ALL
                  SELECT '6' id
                  FROM DUAL)
    SELECT TO_CHAR (l)
    FROM (SELECT LEVEL l
          FROM DUAL
          WHERE LEVEL >= (SELECT MIN (id)
                     FROM test)
          CONNECT BY LEVEL <= (SELECT MAX (id)
                               FROM test))
    MINUS
    SELECT id
    FROM test
  • ORA_AJ
    ORA_AJ Member Posts: 2,295
    Hi mahesh

    I am using Oracle 8.1.5.0 and whenever i uses your query containing Connect by level I gets an errror saying Ora-01436 : CONNECT BY loop in user data..

    Any comments about this.

    --Ajay Sharma
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    CONNECT BY LEVEL was not available in 8.1.5. In 8.1.5, the simplest solution would be to select a ROWNUM from a table that has more rows than the TEST table. Assuming that DBA_OBJECTS is sufficiently large
    SELECT rownum id
      FROM dba_objects
    MINUS
    SELECT id
      FROM test
    As an FYI, if you're using an ancient version of Oracle like 8.1.5, it's helpful to mention that up front. There have been a lot of advances in the 8 major releases since then.

    Justin
  • 788759
    788759 Member Posts: 3
    Mahesh Kaila wrote:

    Try this one



    <PRE class=jive-pre><CODE class="jive-code jive-java">WITH test AS (SELECT <FONT color=navy>'1'</FONT> id

    FROM DUAL

    UNION ALL

    SELECT <FONT color=navy>'2'</FONT> id

    FROM DUAL

    UNION ALL

    SELECT <FONT color=navy>'4'</FONT> id

    FROM DUAL

    UNION ALL

    SELECT <FONT color=navy>'5'</FONT> id

    FROM DUAL

    UNION ALL

    SELECT <FONT color=navy>'6'</FONT> id

    FROM DUAL)

    SELECT TO_CHAR (l)

    FROM (SELECT LEVEL l

    FROM DUAL

    CONNECT BY LEVEL &lt;= (SELECT MAX (id)

    FROM test))

    MINUS

    SELECT id

    FROM test

    </CODE></PRE>



    Regards,

    Mahesh Kaila
    Where can I find the related documentation? It has been solved, but why's that? Many thanks to your <A class=bodylinkwhite href="http://www.software-to-convert.com/myspace-video-conversion-software/myspace-video-to-iriver-lplayer-software.html"><FONT face=tahoma,verdana,sans-serif color=#000 size=1>explanation</FONT></A>!
This discussion has been closed.