Home > IT Answers > Development > Update a subquery with different tables
Bartbart
50 pts.
 Update a subquery with different tables
SQL Query, SQL tables, Subquery
I am trying to update a table and use a subquery for the argument. I think I have both queries correct but since but since they are from two tables I am not tying together correctly. Can you tell me what I need to do?

update MVXJDTA.MITMAS set MMSTAT ='01' where MVXJDTA.MITMAS.MMITNO = MVXJDTA.MITSTA.MHITNO (Select MHITNO as Item, SUM(MHMAQT+MHPUQT + MHSOQT + MHUSQT + MHDEMQ)as total from MVXJDTA.MITSTA where MHCYP6 Between 200801 and 201102 Group by MHITNO Having SUM(MHMAQT+MHPUQT + MHSOQT + MHUSQT + MHDEMQ) <= 0)



Software/Hardware used:
winSql
ASKED: February 17, 2011  8:04 PM
UPDATED: February 18, 2011  11:19 PM
RELATED QUESTIONS
Answer Wiki:
Last Wiki Answer Submitted:  Be the first to answer this question.
All Answer Wiki Contributors:  Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

What database product are you using ?

carlosdl
 63,580 pts.
 

Also, could you please explain what you are trying to accomplish (with words) ?

Maybe something like this is what you are looking for:

update MVXJDTA.MITMAS 
set MMSTAT ='01' 
where MVXJDTA.MITMAS.MMITNO IN
(Select MHITNO
from MVXJDTA.MITSTA 
where MHCYP6 Between 200801 and 201102 
Group by MHITNO 
Having SUM(MHMAQT+MHPUQT + MHSOQT + MHUSQT + MHDEMQ) <= 0)

Or this (this syntax may not work in all databases):

update MVXJDTA.MITMAS m
set MMSTAT ='01' 
where EXISTS 
(Select 1
from MVXJDTA.MITSTA 
where MHCYP6 Between 200801 and 201102
AND MHITNO = m.MMITNO
Group by MHITNO 
Having SUM(MHMAQT+MHPUQT + MHSOQT + MHUSQT + MHDEMQ) <= 0)
carlosdl
 63,580 pts.
 

Carlos,
I have not had a chance to try but the goal is to pull history information on parts (MMITNO or MHITNO) and if no usage in the past 2 years (or some time frame) then change the status (MMSTST) to 01.
We are on MS SQL 2005

Bartbart
 50 pts.
 

Carlos,
After trying the First option,,, It worked. Thanks for your help.
I thought I’d have to connect the items prior to the sub query but i did not.

Thanks again.

Bartbart
 50 pts.
 

Great.

You are welcome.

Different options could produce different response times. If this is going to be run regularly, it would be a good idea to look for the most efficient alternative.

carlosdl
 63,580 pts.