Geek Answers Handbook

How to delete a row that has only one view in MySql?

I have the following data in a MySQL table called info:

chapter | section
     3  | 0 
     3  | 1
     3  | 2
     3  | 3
     4  | 0
     5  | 0

I would like to delete the row for chapter = n, but only when there is no section> 0 for the same chapter. Thus, chapter 3 cannot be deleted if chapters 4 and 5 can be deleted. I know the following does not work:

DELETE info WHERE chapter = 3 AND NOT EXISTS (SELECT * FROM info WHERE chapter = 3 AND section>0);

The same table is used twice in the instruction. So what is the easiest way to achieve my goal?

+4
source share
3 answers

You have an idea correctly. Here is the syntax:

DELETE
FROM mytable
WHERE chapter NOT IN (
    SELECT * FROM (
        select tt.chapter
        from mytable tt
        where tt.section <> 0
        group by tt.chapter
    ) tmp
)

Nested selection is a workaround in MySQL.

Demo version

+3
source

, , , .

DELETE FROM table1 WHERE table1.chapter Not IN (select chapter from (SELECT table1.chapter FROM table1 WHERE Table1.section >=1 ) Results);

sub query where, . where, .

+1

That should do it.

DELETE FROM Table t WHERE NOT EXISTS( SELECT 1 FROM Table t2 Where t2.chapter = t.chapter And t2.section > 0 ) In my experience it's Existsusually better than In. If you keep a large number of records, you should take this into account.

0
source

More articles:


All Articles