Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

update a column within a join statement and group by clause

Posted on 2013-11-04
6
Medium Priority
?
232 Views
Last Modified: 2013-11-05
I have pulled up the following recordset.

SELECT Max(Text4)Text4,Max(Text5)Text5, Sum(Total)TotalsperStudent FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '" & Fiscal & "' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = '" & Com(z) & "' And NewPart = 0 And Combo11 like '" & rec1.Fields(0) & "' Group by tblRegistration.ID HAVING Sum(Total)>0

Open in new window


Within this recordset, I would like to update the column called NewPart = 1 in the table called AttendanceGrid.  

How is this possible?
0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39622973
update AttendanceGrid set NewPart = 1 Where AttendanceGrid.[ID] in (SELECT Max(Text4)Text4,Max(Text5)Text5, Sum(Total)TotalsperStudent FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '" & Fiscal & "' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = '" & Com(z) & "' And NewPart = 0 And Combo11 like '" & rec1.Fields(0) & "')
0
 

Author Comment

by:al4629740
ID: 39623293
Sorry, I meant to say "NewPart = 1 in the table called tblRegistration.

Would it be:

update AttendanceGrid set NewPart = 1 Where tblRegistration.[ID] in (SELECT Max(Text4)Text4,Max(Text5)Text5, Sum(Total)TotalsperStudent FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '" & Fiscal & "' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = '" & Com(z) & "' And NewPart = 0 And Combo11 like '" & rec1.Fields(0) & "')
0
 

Author Comment

by:al4629740
ID: 39623457
I tried the following with no success:

update tblRegistration set NewPart = 1 Where tblRegistration.[ID] in (SELECT * FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '2013' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = 'September' And NewPart = 0 And  Total > 0 And Combo11 like 'Alternatives')

Error:
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Assisted Solution

by:QuinnDex
QuinnDex earned 400 total points
ID: 39623566
try this

update tblRegistration set NewPart = 1 Where exists tblRegistration.[ID] in (SELECT * FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '2013' And AttendanceGrid.[CSW/TitleXX] = 'C' And AttendanceGrid.Month = 'September' And NewPart = 0 And  Total > 0 And Combo11 like 'Alternatives')
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 39623855
update tblRegistration set NewPart = 1
where ID in
(
SELECT tblRegistration.ID
FROM AttendanceGrid INNER JOIN tblRegistration
ON AttendanceGrid.[ID]=tblRegistration.[ID]
Where  
AttendanceGrid.Fiscal = '" & Fiscal & "'
And AttendanceGrid.[CSW/TitleXX] = 'C'
And AttendanceGrid.Month = '" & Com(z) & "'
And NewPart = 0
And Combo11 like '" & rec1.Fields(0) & "'
Group by tblRegistration.ID
HAVING Sum(Total)>0  
)
0
 
LVL 12

Accepted Solution

by:
Paul_Harris_Fusion earned 1600 total points
ID: 39623860
Or specifically with your runtime values:

update tblRegistration set NewPart = 1
Where ID in
(
SELECT tblRegistration.ID
FROM AttendanceGrid INNER JOIN tblRegistration
ON AttendanceGrid.[ID]=tblRegistration.[ID]
Where  AttendanceGrid.Fiscal = '2013'
And AttendanceGrid.[CSW/TitleXX] = 'C'
And AttendanceGrid.Month = 'September'
And NewPart = 0
And  Total > 0
And Combo11 like 'Alternatives'
)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question