[SOLVED] SQLite Selecting the first row who has a NULL in a column and changing that column to a different value

SQLite Selecting the first row who has a NULL in a column and changing that column to a different value

Hello I have a somewhat unique problem I need to select the first row that has NULL in the Status column and change its value to the text (processing)

To clarify I will use SQLite in a program that will need to process entries in a database. The columns are URL Name Status

The Name and URL is not something that I can control. The Status column is used for the program all new entries come in with a NULL value I need to mark the first one with the text (processing) and then the program will refer to the row that has a value of (processing) in the status column and after it finishes change it to [FINISHED]

Also I don't want to select any row that also has NULL in the URL column.

I tried this code only its not getting me anywhere

SELECT * FROM List WHERE Status IS NULL ORDER BY ROWID ASC LIMIT 1
update List set Status = replace ( Status, NULL ,'(processing)')
commit;

What I definitively don't want to do is change all NULL values in the column Status at the same time. Only the first.

Solution

You need a WHERE clause in the UPDATE statement:

UPDATE List 
SET Status = '(processing)'
WHERE rowid = (SELECT MIN(rowid) FROM List WHERE Status IS NULL)

If you want also this condition:

Also I don't want to select any row that also has NULL in the URL column.

then:

UPDATE List 
SET Status = '(processing)'
WHERE rowid = (SELECT MIN(rowid) FROM List WHERE Status IS NULL)
  AND URL IS NOT NULL

or:

UPDATE List 
SET Status = '(processing)'
WHERE rowid = (SELECT MIN(rowid) FROM List WHERE Status IS NULL AND URL IS NOT NULL)