How to set all rows in column 0 when all rows reach 1

Question

i have a function that needs to update all rows to 0 after all rows have 1..

I have made some save systhem it needs to work like this. I got multiple employee, after i use a button 2 mails will be send. After the mail send savecounter will update to 1 and if i close the program then he knows where tho start again.

my question now is, how do i make a query that checks if all savecounter rows is 1. and if so update it all to 0. what do i need to do thanxx

def savecountercheck():
    conn = sqlite3.connect('schoonschip.db')
    cursorr =  conn.cursor()

    employe = cursorr.execute('SELECT savecounter FROM employee ')
    selection =  cursorr.fetchall()
    for i in selection:
        if i ==  1:  
            cursorr.execute('UPDATE employee SET savecounter = 0 ' )
    conn.commit()
    conn.close()
    print(selection)

Does this answer your question? [How UPDATE and SELECT at the same time](https://stackoverflow.com/questions/2934369/how-update-and-select-at-the-same-time) — Homer512, Aug 02 '22 at 16:27
That answer is about Postgres but SQlite supports the same syntax, as far as I can tell: https://www.sqlite.org/lang_update.html — Homer512, Aug 02 '22 at 16:29

score 1 · Accepted Answer · answered Aug 02 '22 at 17:18

I assume that 0 and 1 are the only possible values for the column savecounter.

If you want to automate the task, so that every time the column savecounter is updated from 0 to 1 and there is no other 0 in the column then the whole table gets updated with 0s in savecounter, you could create a trigger:

CREATE TRIGGER emp_reset_savecounter AFTER UPDATE OF savecounter ON employee
BEGIN
  SELECT CASE WHEN NEW.savecounter = 0 THEN RAISE(IGNORE) END;
  SELECT CASE WHEN (SELECT MIN(savecounter) FROM employee) = 0 THEN RAISE(IGNORE) END;
  UPDATE employee SET savecounter = 0;
END;

See the demo.

If you want to do it in your app, you will have to execute this UPDATE statement:

UPDATE employee 
SET savecounter = 0
WHERE (SELECT MIN(savecounter) FROM employee) = 1;

See the [demo]2.

How to set all rows in column 0 when all rows reach 1

1 Answers1