Update SQLite table based on data in another table

Khurram Majeed Published at Dev

Khurram Majeed

I have two tables in SQLite which look like this

TABLE_X    
____________________
| id | C1 | C2 | C3 | C4 |
| 10 | 99 | 03 | 04 | 00 |
| 60 | 88 | 20 | 30 | 40 |


TABLE_Y
___________     
| id | C2 |
| 10 | 11 |
| 60 | 22 |

I am trying to write query to update records on Table X based on records in Table Y The condition for the updateis something like the following

update table_x
   set table_x.c1 = 100,
       table_x.c2 = table_y.c2
 where table_x.id = table_y.id

But when I try to do this I get an error message saying

NO such column: table_y.c2

user2864740

The deleted answer was correct about the cause of the error: a relation identifier must be introduced (e.g. with FROM/JOIN) in a query before it can be used.

While SQLite does not support UPDATE..JOIN (so there is no way to introduce the lookup relation directly), a dependent sub-query can be used to simulate the effect:

update table_x
   set c1 = 100,
       c2 = (select y.c2 from table_y as y
             where y.id = table_x.id)

Note that unlike a proper UPDATE..JOIN, if the sub-select fails to find a match then NULL will be assigned.

YMMV.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-4

Comments

0 comments

From Dev

Related Related

Article

Update SQLite table based on data in another table

Update SQLite table based on data in another table

Sqlite - Update table rows based on another data in the row

update table based on another tables value in sqLite

update data.table based on values in another data.table

Update a data.table based on another data table

Fast update or create a table based on data from another table

SQLite - Update a column based on values from another table's columns

How to update another table based on foreign table

Update table with another table based on cell value

How to update table based on another table column?

Update a column in a table based on column in another table

Update table based on conditions from another table

Update table based on another table with conditions

Update one table by aggregating data of another table

Select data based on another table

Trigger to update table based on another table update in mysql

mysql update and insert statements based on another table

informix update based on values in another table

Update another table based on latest record

Trigger to update a count based on another table

How to update one table based on aggregate query form another table

SQL set operation to update table based on values on another table

R update table column based on search string from another table

Update column in one table based on value in another table in mysql

Update mysql one table from another table based on dates

Update mysql table based on another table and file input

Update multiple columns in one table based on values in another table in mysql

R update table column based on search string from another table

MS SQL Server, Create and update a table based on another table

Update, Insert and Delete rows in a table based on changes in another table