Click here to Skip to main content
14,695,499 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
Hello and Good day,

I am a new beginner. I have this challenge in C# and would appreciate if anyone can help me out. I have three tables as described belo;
TABLE A
VehN, Dst, TrpM

TABLE B
VehN

TABLE c
VehN, Tot_Dst

TABLE A DATA
VehN Dst TrpM
UYO123CDF, 2000, OCT
PHC456ABU, 150, OCT
ERT678SER, 250, OCT
ERT678SER, 1500, OCT
UYO123CDF, 501, OCT
KUU8376G, 83155, SEP
KUU8376G, 1500, OCT
LAG123EKY, 3100, OCT
KUU8376G, 601, OCT
PHC456ABU, 500, OCT
PHC456ABU, 801, OCT

TABLE B DATA
VehN
UYO123CDF
PHC456ABU
ERT678SER
KUU8376G
LAG123EKY


I want TABLE C to be updated as sum(table-A.Dst) where first data in Table-B = Table-A.VehN and where Table-A.TrpM = 'OCT' This should continue untill Table-B end of file.


Table C will now look like this
VehN, Tot_Dst
UYO123CDF, 2,501
PHC456ABU, 1,451
ERT678SER, 1,750
KUU8376G, 2,101
LAG123EKY, 3,100

I will need a c# code and SQL command that will enable me achieve what I described above.

Olabode
Posted
Comments
Zoltán Zörgő 1-Nov-15 14:35pm
   
You can use subquery in UPDATE statement. And you can use table aliases to link between WHERE clause in UPDATE and subquery. It is quite simple. But your formulation about the exact situation is not quite clear.
Suvendu Shekhar Giri 1-Nov-15 14:40pm
   
Everything is ready but have you tried anything so far? If yes , then share the relevant code which you are stuck at.

1 solution

Something like this?
UPDATE TableC c
set Tot_Dst = 
(
SELECT SUM(TableA.Dst) FROM TableA a WHERE a.TrpM="OCT" AND a.VehN = c.VehN
)
where c.VehN in (SELECT VehN from TableB)

This is for T-SQL part. For C# part it depends what do you need to pass from code to T-SQL. You can pass month or even TableB if necessary, but than you need to wrap this in a stored proceudre.
   
v2
Comments
Member 11847697 2-Nov-15 14:38pm
   
Thanks Zoltan Zorgo,

When I tried out your suggested solution in SQL Server Management Studio, I got this error message;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'C'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'where'

Could you look into it again and advise what could have gone wrong.

Regards.
Olabode
Zoltán Zörgő 2-Nov-15 15:59pm
   
Hello
It would be helpful if you would post the scipted export of your tables (schema and data) so I can test without having to spend time on rewriting your environment. Please remember that I don't have your database.

But I think the update syntax is different if one needs alias. Try this:

UPDATE c
SET Tot_Dst =
(
SELECT SUM(TableA.Dst) FROM TableA a WHERE a.TrpM="OCT" AND a.VehN = c.VehN
)
FROM TableC c
WHERE c.VehN in (SELECT VehN from TableB)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900