SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Insert this query in a update statement. HELP!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/17/2013 :  10:55:16  Show Profile  Reply with Quote
select s.entry_id, sum(case when p.no_eli_educ > 0
then case when name_of_schools = 'no facility' then 1 else 0 end
else 0
end)
from schools s
inner join payroll p on s.entry_id = p.entry_id
group by s.entry_id



update payroll set number_of_no_schools

"query here"

where p.no_eli_educ > 0


thanks!

VFP9.0 via MySQL 5.0

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 11/17/2013 :  11:00:36  Show Profile  Reply with Quote
quote:
Originally posted by sikharma13

select s.entry_id, sum(case when p.no_eli_educ > 0
then case when name_of_schools = 'no facility' then 1 else 0 end
else 0
end)
from schools s
inner join payroll p on s.entry_id = p.entry_id
group by s.entry_id



update payroll set number_of_no_schools

"query here"

where p.no_eli_educ > 0


thanks!

VFP9.0 via MySQL 5.0

For MySQL 5.0, this would not be the right forum to ask the question. This is a Microsoft SQL Server forum.

Regardless, what you would need is something like this (although I don't think MySQL supports the common table expression quite in the same way). Also, if you have multiple rows in the payroll table for a given entry_id, this may not (or may) be the right query:
;with cte as
(select s.entry_id, sum(case when p.no_eli_educ > 0
then case when name_of_schools = 'no facility' then 1 else 0 end
else 0
end) as no_schools
from schools s
inner join payroll p on s.entry_id = p.entry_id
group by s.entry_id
)
update p set 
	p.number_of_no_schools = c.no_schools
from
	payroll p
	inner join cte c on c.entry_id = p.entry_id

Edited by - James K on 11/17/2013 11:00:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000