How to update join table using sqlite?

Question

I've read many similar threads but still can't make it work.

I have 2 tables, "stores_bars" and "house_data" and I need to update part of their join to make this work that way:

UPDATE (
SELECT good_id FROM stores_bars
INNER JOIN house_data ON SID=ID
WHERE typ = 3 AND owner = ''
)
SET good_id = 0
;

but this throws an syntax error. Literally, the table "house_data" is over 5k rows, the second one is over 1k rows and the select itself has only 220 rows, which I need to assign "0" value to (from that select).

I tried with REPLACE INTO, but in this scenario couldn't SET good_id = 0 as another syntax. I've tried UPDATE FROM clause which as well didn't work:

UPDATE stores_bars FROM
INNER JOIN house_data ON SID=ID
WHERE typ = 3 AND owner = ''
)
SET good_id = 0
;

SQL Error: near "FROM": syntax error is the message.

Maybe it's easier that I think but just stuck on this?

EDIT

According to the syntax this should work:

UPDATE stores_bars 
SET good_id = 0
FROM stores_bars
INNER JOIN house_data ON SID=ID
WHERE typ = 3 AND owner = ''
;

but it doesn't. The error is SQL Error: near "FROM": syntax error. I dunno whether my sqlite is not up to date - don't know how to update it. There's some workaround for this and I'm searching for it.

EDIT 2

I've managed to find proper syntax:

UPDATE stores_bars 
SET good_id = NULL
WHERE SID IN
( SELECT good_id FROM stores_bars
INNER JOIN house_data ON SID=ID
WHERE typ = 3 AND owner = '' )
;

but while that inner query returns 219 results, the whole update got me that message: Query OK, 24 rows affected (0.22 sec).

Should be 219, shouldn't it??

Answer 1

The correct syntax for UPDATE...FROM (requires version 3.33.0 of SQLite) is this:

UPDATE stores_bars 
SET good_id = 0
FROM house_data
WHERE SID = ID AND typ = 3 AND owner = '';

Although it is actually a join, there are no JOIN and ON clauses.

But, it is always better to use the tables' names/aliases as qualifiers in your code. Something like this (although I'm not sure in which table the columns belong):

UPDATE stores_bars AS s 
SET good_id = 0  -- the updated column should not be qualified with the table's alias
FROM house_data AS h
WHERE s.SID = h.ID AND h.typ = 3 AND h.owner = ''; 

For versions of SQLite prior to 3.33.0, use EXISTS:

UPDATE stores_bars AS s 
SET good_id = 0  
WHERE EXISTS (
  SELECT *
  FROM house_data AS h
  WHERE s.SID = h.ID AND h.typ = 3 AND h.owner = ''
);