1

I have table named Student. It have primary key student_id which is auto increment and not null.

Now Student contains around 5000 records.

After passed out from university, we need to delete specific student record from Student and related child table as well.

It is working good.

I need to get those numbers who are not in student_id range.

For ex.

I have 5000 records. So assume range is 1-5000. In Student table, there may be data with primary key :

student_id :

1 2 4 5 6 7 9 12 15 etc...

So I want deleted ids, like : 3, 8, 10, 11, 13, 14, etc... And need this result in some text file.

Finally I have 2 questions :

  • Need query to get those deleted ids
  • Can I export result in some file after fired query ?
  • 1
    If you want the numbers to delete from a child table its even easier, you delete the child where there isn't a student_id match. Like this answer – danblack Sep 12 '18 at 11:25
  • 1
    1) Generate pseudotable with the numbers in a assumed range (or from 1 up to MAX(ID), if 8+ - in CTE). LEFT JOIN your Student table to it by ID and select numbers which not exists in a table. 2) Use SELECT .. INTO OUTFILE. – Akina Sep 12 '18 at 11:49
  • @danblack That I know...But I need from master table only. – VRAwesome Sep 12 '18 at 11:56
  • @Akina Can you help me to write exact query ? – VRAwesome Sep 12 '18 at 11:56
  • 1
    Specify your MySQL version. – Akina Sep 12 '18 at 12:19
2
SELECT n
FROM (
        SELECT 1000*n1+100*n2+10*n3+n4+1 n
        FROM 
                (
                SELECT 0 n1 UNION 
                SELECT 1 UNION 
                SELECT 2 UNION 
                SELECT 3 UNION 
                SELECT 4 UNION 
                SELECT 5 UNION 
                SELECT 6 UNION 
                SELECT 7 UNION 
                SELECT 8 UNION 
                SELECT 9 
                ) n1,
                (
                SELECT 0 n2 UNION 
                SELECT 1 UNION 
                SELECT 2 UNION 
                SELECT 3 UNION 
                SELECT 4 UNION 
                SELECT 5 UNION 
                SELECT 6 UNION 
                SELECT 7 UNION 
                SELECT 8 UNION 
                SELECT 9
                ) n2,
                (
                SELECT 0 n3 UNION 
                SELECT 1 UNION 
                SELECT 2 UNION 
                SELECT 3 UNION 
                SELECT 4 UNION 
                SELECT 5 UNION 
                SELECT 6 UNION 
                SELECT 7 UNION 
                SELECT 8 UNION 
                SELECT 9
                ) n3,
                (
                SELECT 0 n4 UNION 
                SELECT 1 UNION 
                SELECT 2 UNION 
                SELECT 3 UNION 
                SELECT 4 UNION 
                SELECT 5 UNION 
                SELECT 6 UNION 
                SELECT 7 UNION 
                SELECT 8 UNION 
                SELECT 9
                ) n4
        HAVING n < (
                    SELECT MAX(student_id)
                    FROM Student
                   )
     ) numbers
LEFT JOIN Student ON student_id = n
WHERE student_id IS NULL

fiddle

  • It is throwing error. Here it is : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 2 UNION SELECT 3 UNION ' at line 7 – VRAwesome Sep 12 '18 at 13:05
  • 1
    Query text edited, fiddle added. – Akina Sep 12 '18 at 13:29
  • It is working... I got deleted ids. Thanks...!!! – VRAwesome Sep 12 '18 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.