SQLite how to use UPDATE and LIMIT at the same time?

Question

What I'm looking for is to update only 1 row in a table where all the entries are, in the beginning, zero.
After a while surfing the internet for answers, I found out that LIMIT should work fine with UPDATE, but also not quite. From what I've seen, I should have "enabled" some stuff with SQLITE_ENABLE_UPDATE_DELETE_LIMIT, but I have no idea what that is or where and when I should have done it.
I've also seen different solutions for those in my situation, things like:

UPDATE Table_name
Set Column_name= new_value
WHERE Column_name IN 
(   SELECT Column_name
    FROM Table_name
    WHERE Column_name = initial_value
    LIMIT 1
)

But this, for some reason, is not working for me. The LIMIT 1 has absolutely no effect, as the entire column gets modified.

Is it because I'm using the same column name in both SET/SELECT and in WHERE?

My table only consists of a single column.

Answer 1

For this to work, you need to have SQLite compiled with option SQLITE_ENABLE_UPDATE_DELETE_LIMIT enabled.

You could write your query as:

update table_name
set column_name = 'new value'
where column_name = 'initial value'
order by id
limit 1

If you don't have a primary key column, you can remove the order by clause. One arbitrary row will be updated.

update table_name
set column_name = 'new value'
where column_name = 'initial value'
limit 1

You could also filter with a subquery:

update table_name
set column_name = 'new value'
where id = (
    select min(t1.id) 
    from table_name t1 
    where t1.column_name = 'initial value'
)

Answer 2

You can get the minimum (or maximum) rowid of the rows that satisfy your conditions and use it to update only 1 row:

UPDATE Table_name
Set Column_name= new_value
WHERE rowid = (   
  SELECT MIN(rowid)
  FROM Table_name
  WHERE Column_name = initial_value
)