Solved

update a column within a join statement and group by clause

Posted on 2013-11-04
6
214 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 9

Expert Comment

by:QuinnDex
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 9

Assisted Solution

by:QuinnDex
QuinnDex earned 100 total points
Comment Utility
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
Comment Utility
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 400 total points
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now