UPDATE multiple rows using SELECT

Question

I have A table and two rows with id=1 and id=2 and their x parameter is 1. I also have B table and two rows with same id 1 and 2. I am trying to update all of the data(column)on B table which has same id with A table whose x parameter is 1.

A table

id | x |
 1 | 1 |
 2 | 1 |

B table

id | Y  |
 1 | yes|
 2 | yes|

My query is

 UPDATE B SET y='No' WHERE B.id=(SELECT A.id FROM A WHERE A.x=1);

The problem is select returns mutliple data and i can only update the first data. I tried to use JOIN but sqlite gives syntax error near INNER i couldn't find the problem.

 UPDATE B SET B.y='No' INNER JOIN A ON B.id=A.id WHERE A.x=1;

Answer 1

Use this:

UPDATE ... WHERE B.id IN (SELECT A.id ...);