Update on join in Sqlite

Question

I have two two tables A and B in which both P columns are common and I need to use update command in table B only when both p values are same and C column from table A is given

What I am trying is:

update B 
set P =100 
where B.P=A.P 
    and A.C=60

But it's giving me error no such column A.P

possible duplicate of [sql: how to update table values from another table with the same user name?](http://stackoverflow.com/questions/3845718/sql-how-to-update-table-values-from-another-table-with-the-same-user-name) — Aleks G, Jun 26 '13 at 10:30

score 4 · Accepted Answer · answered Jun 26 '13 at 10:31

4

You are updating table B and do not have reference to table A, so sqlite just does not know where to look for. Try this:

UPDATE B
SET    P = 100
WHERE  B.P IN (SELECT A.P
               FROM   A
               WHERE  A.C = 60)

answered Jun 26 '13 at 10:31

mishik

9,973
9
45
67

1

Is this giving you output? `SELECT * FROM B WHERE B.P IN (SELECT A.P FROM A WHERE A.C = 60)` – mishik Jun 26 '13 at 10:53

score 1 · Answer 2 · answered Jun 26 '13 at 10:33

1

You can do it like this

Update B set P = 100 WHERE B.P = (Select P from A WHERE C = 60)

answered Jun 26 '13 at 10:33

NetStarter

3,189
7
37
48

Update on join in Sqlite

2 Answers2