Using the results of a SQL query as parameters for a second one

Question

Our SQL developer put together the following query to basically pull a list of any employee who has logged into our system today. This query works perfectly and will spit out a list of names. What I need to do is take the list of names it spits out and then use those in a new query to change a column on a different table for each of those names.

select distinct(t.CC_FullName) as Employee,
t.CC_Supervisor as Supervisor,
t.StaffCimID
from (
select s.*,
r.CC_FullName,
r.CC_Supervisor,
r.StaffCimID
from (
select AgentFirstName + ' ' + AgentLastName as AgentName,
Agent
from pia.dbo.Five9IntraDayExtract with(nolock)
group by AgentFirstName + ' ' + AgentLastName,
Agent
) s
inner join pia.dbo.StaffInformationNew r with(nolock)
    ON CASE
        WHEN s.Agent LIKE '%_manual' AND s.Agent = r.Five9Name_MTM THEN 1
        WHEN s.Agent NOT LIKE '%_manual' AND s.Agent = r.Five9Name THEN 1
        ELSE 0
        END = 1
        and r.EndDate is null
 ) t
 where t.CC_FullName is not null
 and t.StaffCimID is not null
 order by t.CC_FullName, t.CC_Supervisor

so basically after that runs I get a list with three columns. I need to take the name column and basically do the following:

Update Attendance Set Seated = '1' where name =   'John Doe'

I need to do that for every result from the initial query. What's the best way to do that?

You can do an `UPDATE` from the query. Possible duplicate (https://stackoverflow.com/questions/6629088/bulk-record-update-with-sql) — Ryan Wilson, Oct 15 '18 at 18:37

score 3 · Accepted Answer · answered Oct 15 '18 at 18:38

Add that to the top of your query...

Update Attendance 
Set Seated = '1' 
where name in 
(select t.CC_FullName from (
    select s.*,
    r.CC_FullName,
    r.CC_Supervisor,
    r.StaffCimID
    from (
    select AgentFirstName + ' ' + AgentLastName as AgentName,
    Agent
    from pia.dbo.Five9IntraDayExtract with(nolock)
    group by AgentFirstName + ' ' + AgentLastName,
    Agent
    ) s
    inner join pia.dbo.StaffInformationNew r with(nolock)
        ON CASE
            WHEN s.Agent LIKE '%_manual' AND s.Agent = r.Five9Name_MTM THEN 1
            WHEN s.Agent NOT LIKE '%_manual' AND s.Agent = r.Five9Name THEN 1
            ELSE 0
            END = 1
            and r.EndDate is null
     ) t
     where t.CC_FullName is not null
     and t.StaffCimID is not null)

This worked perfectly. I can't believe it was that simple and didn't occur to me. I was assuming I needed to put something after the query, not before. Thank you! — Jb83, Oct 15 '18 at 18:50

Using the results of a SQL query as parameters for a second one

1 Answers1