update table data in sqlite

Question

I have to Tables sale and stock

sale

stock

idStock   sold
1000        0 
1001        0 
1002        0 
1003        0

how to update stock table like this (count total sale items and update the sold column in stock)

idStock   sold
1000        2 
1001        1 
1002        2
1003        1

What have you tried? Generally we provide greater assistance to those who show what they themselves have tried and failed with. We don't want to code it for you, we want to help you code it and learn! — xQbert, Apr 11 '17 at 15:12
http://stackoverflow.com/questions/11790595/sqlite-inner-join-update-using-values-from-another-table for some guideance — xQbert, Apr 11 '17 at 15:18

score 0 · Answer 1 · edited Apr 11 '17 at 15:21

0

UPDATE stock
SET stock.sold = sale_1.myCount
FROM (SELECT idStock, count(*) as myCount
      FROM sale
      GROUP BY idStock) as sale_1
INNER JOIN stock on sale_1.idStock = stock.idStock

I'm doing this from memory. I hope it works. Let us know.

edited Apr 11 '17 at 15:21

xQbert

34,733
2
41
62

answered Apr 11 '17 at 15:19

Edgar

339
1
2
10

UPDATE…FROM is not valid SQL. – CL. Apr 11 '17 at 15:23
Sorry sir. UPDATE stock SET stock.sold = (SELECT myCount FROM (SELECT idStock, count(*) as myCount FROM sale GROUP BY idStock) as x WHERE stock.idStock = x.idStock) Follow this example: UPDATE tbl1 SET col2 = (SELECT col2 FROM tbl2 WHERE tbl2.col1 = tbl1.col1) Again from my head. I hope you can sort the errors – Edgar Apr 11 '17 at 15:36

update table data in sqlite

1 Answers1