Python SQL Lite update using select query

Question

How to update a table column with a value from another table in sql lite on python.

example,

table1

id name value brand
1  n1   v1    -
2  n2   v2    -

table2

id brand
1  b1

i want to update the table1 brand = b1 for id=1

i am trying to do this

UPDATE table1 r join table2 p on r.id= p.id set r.brand=p.brand

but python throws an error on this statement execution

sqlite3.OperationalError: near "r": syntax error

Any solutions please

`UPDATE table1 SET table1.brand = (SELECT table2.brand FROM table2 WHERE table2.id = table1.id)`? http://stackoverflow.com/questions/3845718/sql-how-to-update-table-values-from-another-table-with-the-same-user-name — Cyclonecode, Apr 02 '16 at 21:56
It doesnt work . sqlite3.OperationalError: near ".": syntax error — Saravana, Apr 02 '16 at 22:20

score 0 · Accepted Answer · answered Apr 03 '16 at 00:34

0

I believe you need to remove the . from the left-hand side of the SET clause:

UPDATE table1
    SET brand = (
        SELECT table2.brand FROM table2 WHERE table2.id = table1.id
    );

P.S. Nothing about this question seems Python-specific, so I suggest removing the Python tag.

answered Apr 03 '16 at 00:34

ChrisP

I tried this in Python sql lite, so I thought it would be be meaningful – Saravana Apr 03 '16 at 00:54
@Saravana, Not a problem; you didn't know the issue wasn't something related to the `sqlite` Python module. Did this solve the issue for you? If so, please accept the answer. – ChrisP Apr 03 '16 at 00:56
Will try this and let know . Thanks for the tip – Saravana Apr 03 '16 at 01:00

1 Answers1