1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
Enigma
  • 31
  • 3

1 Answers1

0

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)
forpas
  • 160,666
  • 10
  • 38
  • 76
  • I struggled to make the code you presented work, yet I can not, can you write the full statement that I needed to do this task ? – Enigma Dec 04 '21 at 12:17
  • @Enigma What do you mean by *full statement*? Each of my queries is a full statement. If you *can't make it work* then this means that you did not explain correctly your problem. Edit your question with sample data and expected results to clarify what you want. Better use a fiddle: https://dbfiddle.uk/?rdbms=sqlite_3.27 for your data. – forpas Dec 04 '21 at 12:37
  • The command gives me errors and executes nothing. Sample data https://pastebin.com/xGzNi9DT – Enigma Dec 04 '21 at 12:49
  • @Enigma as you can see here: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=b45d68f037469503e79d4efcd7d74a54 all queries are syntactically correct. If you get errors this means that you are doing something wrong. – forpas Dec 04 '21 at 12:52
  • The statement doesn't execute anything on the DB. I get an error on UPDATE. – Enigma Dec 04 '21 at 12:53
  • @Enigma As I said. Either you did not clearly explain what you want or you are doing something wrong with my code. – forpas Dec 04 '21 at 12:54
  • "Either you did not clearly explain" what exactly is your code going to do ? Can you execute it on the sample DB I have given ? – Enigma Dec 04 '21 at 12:56
  • @Enigma what sample db? – forpas Dec 04 '21 at 12:58
  • https://pastebin.com/xGzNi9DT – Enigma Dec 04 '21 at 12:59
  • @Enigma https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=5b403f43e8396d124918a08da12d1a5c – forpas Dec 04 '21 at 13:02
  • Thanks it works. I think I got confused when you wrote "You need a WHERE clause in the UPDATE statement:" and tried to frankenstain your code into my code. – Enigma Dec 04 '21 at 13:05