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

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)

CodePudding user response：

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.