Combining Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining Queries

Author

Topic

Otacustes
Starting Member

15 Posts

Posted - 2008-09-01 : 09:07:50

Hi all,

I have the following tables and need to update data between them:

TableA

Desc PrevQty
abc
def
ghi
xyz

TableB

Desc Qty
abc 10
def 15
ghi 20
abc 30
xyz 10

I need help with a query to get the total Qty from tableB and update the PrevQty in TableA.

When the query is run, TableA should look like this:

TableA

Desc PrevQty
abc 40
def 15
ghi 20
xyz 10

so far I have this for the query:

SELECT Desc, sum(Qty) as TotalQty
FROM TableB
Group By Desc;

This obviously gives me the total qty for each desc - the trouble I am having is combining this with an update query to modify TableA - I hope I am half way there :)

Any help or guidance offered is greatly appreciated.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 09:10:53

UPDATE T1
set PrevQty=T2.TotalQty
FROM TableA as T1 inner join
(SELECT Desc, sum(Qty) as TotalQty
FROM TableB
Group By Desc
) as T2 on T1.desc=T2.desc;

Madhivanan

Failing to plan is Planning to fail

Otacustes
Starting Member

15 Posts

Posted - 2008-09-02 : 11:01:03

quote:
Originally posted by madhivanan

UPDATE T1
set PrevQty=T2.TotalQty
FROM TableA as T1 inner join
(SELECT Desc, sum(Qty) as TotalQty
FROM TableB
Group By Desc
) as T2 on T1.desc=T2.desc;

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan, but MS Access is complaining!!
Is this the correct syntax for MS Access? I am not sure what the differences are?

Any help or guidance offered is greatly appreciated.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-02 : 11:05:29

Try this

UPDATE T1 FROM TableA as T1 inner join
(SELECT Desc, sum(Qty) as TotalQty
FROM TableB
Group By Desc
) as T2 on T1.desc=T2.desc
set PrevQty=T2.TotalQty

Madhivanan

Failing to plan is Planning to fail

Otacustes
Starting Member

15 Posts

Posted - 2008-09-02 : 11:21:15

quote:
Originally posted by madhivanan

Try this

UPDATE T1 FROM TableA as T1 inner join
(SELECT Desc, sum(Qty) as TotalQty
FROM TableB
Group By Desc
) as T2 on T1.desc=T2.desc
set PrevQty=T2.TotalQty

Madhivanan

Failing to plan is Planning to fail

This doesn't work either...

The error given is "Syntax error in UPDATE statement" and highlights the FROM keyword!!

Any help or guidance offered is greatly appreciated.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-04 : 03:13:29

UPDATE DISTINCTROW TableA as T1 inner join
(SELECT Desc, sum(Qty) as TotalQty
FROM TableB
Group By Desc
) as T2 on T1.desc=T2.desc
set PrevQty=T2.TotalQty

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-04 : 04:09:51

If using MS Access, please post in proper forum.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources