CODEWITHSUNDEEP
sqlitecorrelated-subquery
martin_m
martin_m

Reputation: 137

SQLite update: correlated sub-query fills rows in target column with same values

I am trying to update a column of a table with values of another table's column, depending on the values of the updated column in SQlite. Here is an example:

Table 1
A
1
2
3
1
3
4

Table 2
A   B
1   x
2   x
3   y
4   y

Result Table 1 after query:
A
x
x
y
x
y
y

From related questions, I tried the following queries:

update Table1 set A=(select B from Table2 where(A=Table2.A))

This fills col A of Table 1 with all x.

Also, another version I found here did not work, as it threw the error "no such columns: table2.B":

update Table1 set A=Table2.B where(A=Table2.A)

Any help is highly appreciated!

Upvotes: 0

Views: 591

Answers (2)

devotee
devotee

Reputation: 127

Indeed, you may not need the join there. This should work

update Table1 set A = (select B from Table2 where Table1.A = Table2.A)

Upvotes: 0

CL.
CL.

Reputation: 180192

When you write a column like A without a table reference, the column matches the innermost table. In this case, the innermost table with a column named A is Table2, so the query has the same effect as if you had written:

update Table1 set A = (select B from Table2 where Table2.A = Table2.A)

This matches every row in Table2 (and the database just takes the first one).

When you have the same column name in multiple tables, it is a good idea to always use table names:

update Table1 set A = (select B from Table2 where Table2.A = Table1.A)

Upvotes: 1

Related Questions