Update a subquery with different tables

50 pts.
Tags:
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
Related Questions

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    What database product are you using ?
    70,220 pointsBadges:
    report
  • carlosdl
    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)
    70,220 pointsBadges:
    report
  • Bartbart
    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
    50 pointsBadges:
    report
  • Bartbart
    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.
    50 pointsBadges:
    report
  • carlosdl
    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.
    70,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following