How can I UPDATE table using condition WHERE from another table in SQLite-CodePudding

I'm working with SQLite and I have two tables:

   table1                     table2
-------------             --------------
id                        id
value                     condition

Column "id" contains the same data. And I need to:

UPDATE table1
SET table1.value = 'Some value'
WHERE table2.condition = 'Some condition"

I tried to use JOIN and FROM for linking tables with "id" column, but this isn`t working in SQLite. Please help with syntax.

CodePudding user response：

You need a correlated subquery in the update, something like this:

UPDATE table1
SET value = 'Some value'
WHERE EXISTS (SELECT 1 FROM table2 t2
              WHERE t2.id = table1.id AND
                    t2.condition = 'Some condition');

CodePudding user response：

Method 1 : Considering the two tables you have are "Table1" and "Table2", Updating Table1 can be done using a nested Select statement in which you will be selecting the data you need to update from Table2. For example:

UPDATE Table1
SET StudentID =
(
  SELECT RegNo
  FROM Table2 t
  WHERE StudentID = RegNo
);

You can have a look on this link which solves a similar question : https://dba.stackexchange.com/questions/206317/update-values-in-one-table-from-data-in-another

Method 2 : You can use a table join. Refer the same link given above.