sqlite query question/problem with update table

July 6, 2017 at 15:48:52
Specs: Windows 10
Hi,

I have an SQLite database setup to learn database programming.

I have a table PROJECT which contains:
ID;IDENT;MAT;DESIG;WEIGHT;LENGTH;TRANSPORT
1;101550;S235JR;PLATE;13,628;4340;Transport 1
2;101592;S235JR;PLATE;0,238;75;Transport 1
3;100316;S235JR;BEAM;29,144;2578,21;Transport 5
4;100030;S235JR;BEAM;28,842;2551,52;Transport 2
5;101408;S235JR;BEAM;2,11;560;Transport 1
6;101286;S235JR;BEAM;2,11;560;Transport 1
7;101333;S235JR;GIRDER;2,11;560;Transport 3
8;100896;HOLZ;STEP;2,282;600;Transport 4
9;101074;HOLZ;STEP;1,44;600;Transport 1
10;100989;HOLZ;STEP;1,44;600;Transport 1

I have a second table TRANSPORTID which contains following:
ID;TRUCK;TOTALWEIGHT;TRUCKDATE
1;Transport 1;;
2;Transport 2;;
3;Transport 3;;
4;Transport 4;;
5;Transport 5;;
6;Transport 6;;
7;Transport 7;;
8;Transport 8;;
9;Transport 9;;
10;Transport 10;;

I want to update table TRANSPORTID with the total combined weight of all matching values in table PROJECT in column TRANSPORT

Items in table PROJECT TRANSPORT match table TRANSPORTID TRUCKS then insert the total weight of all fields that contain the corresponding value (Tranport1, ...)

So far, I can run this, which gives a correct result.

select TRANSPORT, sum(WEIGHT)
from PROJECT
group by TRANSPORT;

To update table TRANSPORTID, I came up with this, but this gives the total weight of ALL fields from table PROJECT, which is not what I want:

update TRANSPORTID
set TOTALWEIGHT =
(select sum(WEIGHT)
from PROJECT group by TRANSPORT);

What do I have to add to get the correct result (first query) to a correct update ??

Thanks


See More: sqlite query question/problem with update table

Reply ↓  Report •
Related Solutions


Ask Question