How do i delete these rows in SQLite database?

I have a table in a SQLite database as below:

+-------+------------+---------------+-------+------------+
| ROWID | student_id | qualification | grade | date_stamp |
+-------+------------+---------------+-------+------------+
|     1 |     000001 | Mathematics   | A     | 2022-04-01 |
|     2 |     000002 | NULL          | NULL  | 2022-03-01 |
|     3 |     000003 | Physics       | B     | 2022-03-01 |
|     4 |     000003 | NULL          | NULL  | 2022-02-01 |
+-------+------------+---------------+-------+------------+

It is a table of student exam results, if a student has a qualification in a subject it appears in the table as ROW #1. If a student has no qualifications it appears in the table as ROW #2.

ROW #3 & #4 refer to a student (id 000003) who previously had no qualifications in the database, but now has a B in Physics. I need to delete ROW #4 based on the fact that this now has a qualification and the NULL values are no longer appropriate. ROW #2 for student 000002 should be unaffected.

The date_stamp column just shows when that record was last updated.

Appreciate any help, thanks in advance.

Asked by: Dominik376 | Posted: 30-11-2021

Answer 1

You may try doing a delete with exists logic:

DELETE
FROM yourTable
WHERE qualification IS NULL AND
      EXISTS (
          SELECT 1
          FROM yourTable t
          WHERE t.student_id = yourTable.student_id AND
                t.qualification IS NOT NULL AND
                t.date_stamp > yourTable.date_stamp
      );

Answered by: Ted355 | Posted: 01-01-2022

Similar questions

python - Sometimes can't delete an Oracle database row using Django

I have a unit test which contains the following line of code Site.objects.get(name="UnitTest").delete() and this has worked just fine until now. However, that statement is currently hanging. It'll sit there forever trying to execute the delete. If I just say print Site.objects.get(name="UnitTest") then it works, so I know that it can retrieve the site. ...

python - Delete a child object in Django database

Costumer and Worker models. Costumer inherited from User, and Worker inherited from Costumer, when I do worker.delete(), it will delete all the related object in all three tables, how can I do, it will only delete the data in Worker table, and data in rest of tables remain(assume, one costumer doesnt want be a worker longer, but he still want to be a costumer)

python - After delete database ,my Django does't work

I have some problem with my database, so I deleted db.sqlite3 and Migrations manually. than I recreate the database by using manage.py makemigrations <appname> manage. py migrate <appname> Everything looks normal，but when I get into localhost:8000, It is a blank page without anything (even if I never change the templates). To my confused, I can g...

python - can't delete item from database in django view

i created a view to delete an object through a template but it seems im missing something i cant quite put together def product_delete_view(request, id): obj = get_object_or_404(Product, id=id) if request.method == "POST": obj.delete() return redirect('../../') context = { "obj": obj } return render(request, "products/product_delete.html", context) ...

python - How to delete record from database by using input form in django

So i have this small web program in which a user can enter his/her email and receives some updates. Nevertheless this email is saved in the database (sqlite3) and i have a form in which a user can unsubscribe, my problem is i'm trying to find a way to take the input from the unsubscribe form & if it matches any record in the database it deletes it. Does anyone have any suggestions? This is the ...

python - How can I delete a row from sqlite database using flask?

The user enters input and it gets inserted to my database and li element but I want the row to be deleted once the user clicks on it or selects it and then click on a certain button {% for i in results%} <div class="container pt-3 pb-3 bg-primary my-4 bg-gradient-light"> <ul> <li>{{i.name}} {{i.quantity}}</li> </ul> </div> {% endfor%}

python - Add delete raw button to table from database Django

I have a form on the page that adds data to the database and displays it in a table I need to add a button that will delete a specific row by id from the database index.html <tbody> {% for expense_item in expense_items %} <tr> <td>{{ expense_item.username}}</td> ...

python - How do I test a django database schema?

I want to write tests that can show whether or not the database is in sync with my models.py file. Actually I have already written them, only to find out that django creates a new database each time the tests are run based on the models.py file. Is there any way I can make the models.py test use the existing database schema? The one that's in mysql/postgresql, and not the one that's in /myapp/models.py ?

python - Using user input to find information in a Mysql database

I need to design a program using python that will ask the user for a barcode. Then, using this barcode, it will search a mysql to find its corresponding product. I am a bit stuck on how to get started. Does anyone have any tips for me?

python - Import XML into SQL database

I'm working with a 20 gig XML file that I would like to import into a SQL database (preferably MySQL, since that is what I am familiar with). This seems like it would be a common task, but after Googling around a bit I haven't been able to figure out how to do it. What is the best way to do this? I know this ability is built into MySQL 6.0, but that is not an option right now because it is an alpha development rel...

python - Using 'old' database with django

I'm using a hand built (Postgres) database with Django. With "inspectdb" I was able to automatically create a model for it. The problem is that some tables have multiple primary keys (for many-to-many relations) and they are not accessible via Django. What's the best way to access these tables?

database - python orm

This is a newbie theory question - I'm just starting to use Python and looking into Django and orm. Question: If I develop my objects and through additional development modify the base object structures, inheritance, etc. - would Django's ORM solution modify the database automatically OR do I need to perform a conversion (if the app is live)? So, I start with a basic Phone app Object person: name, address, city, s...

python - Large Sqlite database search

How is it possible to implement an efficient large Sqlite db search (more than 90000 entries)? I'm using Python and SQLObject ORM: import re ... def search1(): cr = re.compile(ur'foo') for item in Item.select(): if cr.search(item.name) or cr.search(item.skim): print item.name This function runs in more than 30 seconds. How sh...

abap - Query SAP database from Python?

Closed. This question needs details or clarity. It ...

A database for python 3?

I'm coding a small piece of server software for the personal use of several users. Not hundreds, not thousands, but perhaps 3-10 at a time. Since it's a threaded server, SQLite doesn't work. It complains about threads like this: ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140735085562848 and this is thread id 43...

python - URLs stored in database for Django site

I've produced a few Django sites but up until now I have been mapping individual views and URLs in urls.py. Now I've tried to create a small custom CMS but I'm having trouble with the URLs. I have a database table (SQLite3) which contains code for the pages like a column for header, one for right menu, one for content.... so on, so on. I also have a column for the URL. How do I get Django to call the information in...

database - Problem opening berkeley db in python

I have problems opening a berkeley db in python using bdtables. As bdtables is used by the library I am using to access the database, I need it to work. The problem seems to be that the db environment I am trying to open (I got a copy of the database to open), is version 4.4 while libdb is version 4.6. I get the following error using bsddb.dbtables.bsdTableDB([dbname],[folder]): (-30972, "DB_VERSION...

database - Store simple user settings in Python

I am programming a website in which users will have a number of settings, such as their choice of colour scheme, etc. I'm happy to store these as plain text files, and security is not an issue. The way I currently see it is: there is a dictionary, where all the keys are users and the values are dictionaries with the users' settings in them. For example, userdb["bob"]["colour_scheme"] would have the value "b...

python - Given an rpm package name, query the yum database for updates

I was imagining a 3-line Python script to do this but the yum Python API is impenetrable. Is this even possible? Is writing a wrapper for 'yum list package-name' the only way to do this?

python - How do I notify a process of an SQLite database change done in a different process?

Let's say I have two or more processes dealing with an SQLite database - a "player" process and many "editor" processes. The "player" process reads the database and updates a view - in my case it would be a waveform being mixed to the soundcard depending on events stored in the database. An "editor" process is any editor for that database: it changes the database constantly. Now I want the player t...

Still can't find your answer? Check out these communities...

top

How do i delete these rows in SQLite database?

Answer 1

Similar questions

Still can't find your answer? Check out these communities...

© Patterns & Practices 2022 Rights Reserved | Contact us