SQLite inserting a select into a column

Question

I would need help with a following case:

I have a table called Prevodnik with these columns:

Chaincode | Shopcode  
2225 | XX2250  
5552 | XX5548

Then there is a table called Prevodnik2 with following:

Chaincode | Shopcode  
2225 |   
5552 |

I need to join the shopcode records into the empty column Shopcode, but I can't make it work. So far I've tried something similar to this:

update prevodnik2 set shopcode = (select prevodnik.shopcode from prevodnik LEFT JOIN prevodnik2 ON prevodnik2 = prevodnik.chaincode)

Would you happen to know what to do with this, please?

Thanks a lot!

check this out: http://stackoverflow.com/questions/773441/how-do-i-make-an-update-while-joining-tables-on-sqlite also http://stackoverflow.com/questions/3845718/sql-how-to-update-table-values-from-another-table-with-the-same-user-name — Nebi, Sep 09 '16 at 06:20
why two tables with the same content in the first place? Wrong design — e4c5, Sep 09 '16 at 06:26

score 0 · Accepted Answer · answered Sep 09 '16 at 06:22

The subquery leads to multiple rows, but only one is needed in the SET-clause. You can try the following. I don't know if SQLLite supports everything in this statement.

update prevodnik2 p2 set shopcode = (select p.shopcode from prevodnik p WHERE p2.chaincode = p.chaincode)

SQLite inserting a select into a column

1 Answers1