Q:

Update a sqlite database while select into a while loop

There is a bash script that will do "some magic"™ to files which will be referenced to a sqlite database and after doing "some magic"™ the database should be updated. Here are the simplified code
sqlite3 database.db "select NUMBER from table WHERE STATUS = 'N'" | while read line; do
    SELECTION=$(echo $line | awk -F'|' '{ print $1 }')
    [some magic]™
    sqlite3 database.db "update table SET STATUS='Y' WHERE NUMBER='$SELECTION'"
done

Everything works, the sqlite database will be read line by line and I could do "some magic"™ to the referenced files, but I can´t update the line in the database - I´ve got an error:

Error: database is locked

Anybody knows how I could update a database while reading a database? Or are there another solution how I could do this?

A:

if you don't want to move to a real SQL database, and temporarily storing the result is not possible, you'll have to make sure you don't run concurrent SELECT and UPDATE on the same table.
see LIMIT directive for SELECT.
so you'd do something like:
line=x
while [ -n "$line" ]
do
    line=$(sqlite3 database.db "select NUMBER from table WHERE STATUS = 'N'" LIMIT 1)
    SELECTION=$(echo $line | awk -F'|' '{ print $1 }')
    [some magic]™
    sqlite3 database.db "update table SET STATUS='Y' WHERE NUMBER='$SELECTION'"
done

That would make SELECT always return just one result and finish, which you would then process and UPDATE, and rerun SELECT to get next result (as STATUS changed, SELECT would get next value, not the old one as it no longer matches "N")
or maybe you could do "some magic (tm)" via SQL instead of a shell, so you could offload all the work to SQL engine...

