Update Question

[GuyInTn]

I have a query that grabs records by comparing information on two different databases:

SELECT
ESS.EmailJacketKey,
COUNT(DISTINCT ESS.EmailWizardStepKey),
EmailJacket.Name,
EmailJacket.Completed,
EmailJacket.ModificationDate
FROM
EmailWizardStepStatus AS ESS INNER JOIN EmailJacket AS EmailJacket
ON ESS.EmailJacketKey = EmailJacket.EmailJacketKey
WHERE
ESS.Completed = 1 AND EmailJacket.Queued = 0
GROUP BY
ESS.EmailJacketKey, EmailJacket.Name, EmailJacket.ModificationDate, EmailJacket.Completed
HAVING
COUNT(DISTINCT ESS.EmailWizardStepKey) = (SELECT MAX(StepNumber) FROM DevPortal..EmailWizardStep WHERE WizPath = 1)
ORDER BY
EmailJacket.ModificationDate DESC


I need to update the Completed field in the EmailJacket table. The records returned will have a 0 in the completed field, I need to update that to a 1.

Any ideas?

Thanks,
GuyInTn

[r937]

not sure i understand the question

what's with DevPortal..EmailWizardStep, is that like on a separate server?

okay, so this is a complex query that returns some rows with a 0 in one of the columns

are you asking how to incorporate this result set into a subquery as part of an UPDATE statement?

if it were me, i'd save the result set into a temp table

[StephenBauer]

Isn't funny how when you ask the original poster for more info, quite often you kind of given them the answer(s) if they really thought about it!

As for the DevPortal..EmailWizardStep reference, yeah, that is a table named "EmailWizardStep" in a database named "DevPortal".

The reference could even be:

OtherServer.DevPortal.Schema3.EmailWizardStep

if the "DevPortal" database resided on the "OtherServer" server and was part of the "Schema3" schema.

--

not sure i understand the question

what's with DevPortal..EmailWizardStep, is that like on a separate server?

okay, so this is a complex query that returns some rows with a 0 in one of the columns

are you asking how to incorporate this result set into a subquery as part of an UPDATE statement?

if it were me, i'd save the result set into a temp table

[null]

Do you need to UPDATE the EmailJacket.Completed = 1 just for that view?? (if so use CASE, this isn't really an "UPDATE" though)...or do you actually have to go back to the table and make the change to it? Here's something I threw together real quick, it should work though...

UPDATE  EmailJacket
set     Completed = 1
where   Name in
            (select distinct Name
             from   (SELECT 
                       ESS.EmailJacketKey, 
                       COUNT(DISTINCT ESS.EmailWizardStepKey), 
                       EmailJacket.Name,
                       EmailJacket.Completed,
                       EmailJacket.ModificationDate
                       FROM 
                       EmailWizardStepStatus AS ESS INNER JOIN EmailJacket AS     EmailJacket 
                       ON ESS.EmailJacketKey = EmailJacket.EmailJacketKey
                       WHERE 
                       ESS.Completed = 1 AND EmailJacket.Queued = 0
                       GROUP BY 
                       ESS.EmailJacketKey, EmailJacket.Name, EmailJacket.ModificationDate, EmailJacket.Completed
                       HAVING 
                       COUNT(DISTINCT ESS.EmailWizardStepKey) =
                              (SELECT MAX(StepNumber)
                               FROM   DevPortal..EmailWizardStep
                               WHERE  WizPath = 1)
                     ) as A
              )

[null]

...or w/a join...hopefully someone can confirm this...

UPDATE  EmailJacket EJ
set     Completed = 1
join    (SELECT  ESS.EmailJacketKey, 
                 COUNT(DISTINCT ESS.EmailWizardStepKey), 
                 EmailJacket.Name,
                 EmailJacket.Completed,
                 EmailJacket.ModificationDate
         FROM    EmailWizardStepStatus AS ESS
         JOIN    EmailJacket AS EmailJacket 
                      ON ESS.EmailJacketKey = EmailJacket.EmailJacketKey
         WHERE   ESS.Completed = 1 AND EmailJacket.Queued = 0
         GROUP   BY 
                 ESS.EmailJacketKey, EmailJacket.Name,
                 EmailJacket.ModificationDate, EmailJacket.Completed
         HAVING  COUNT(DISTINCT ESS.EmailWizardStepKey) =
                            (SELECT MAX(StepNumber)
                             FROM   DevPortal..EmailWizardStep
                             WHERE  WizPath = 1)
          ) as A
 on     EJ.name = A.Name

[Chris3125]

Hi all,

Still having a problem getting the desired results. I also think I am going about this the hard way and I think I can do this without using two different databases.

I have my EmailJacket table which has a PK of EmailJacketKey(int) and a column named Completed (bit). My EmailWizardStepStatus table has a PK of EmailWizardStepKey(int), an FK of EmailJacketKey(int), and a column named Completed(bit).

There are an average of 7 rows in the EmailWizardStepStatus table that equal the same EmailJacketKey. I need to be able to go through the EmailWizardStepStatus table and group the rows by the EmailJacketKey, check that all the rows with the same EmailWizardStepStatus.EmailJacketKey have a EmailWizardStepStatus.Completed = 1, if they do, update the row in the EmailJacket table that has the matching EmailJacketKey and set EmailJacket.Completed = 1.

Is this possible?

[r937]

untested:

update EmailJacket
   set Completed = 1
  from EmailJacket t1
inner
  join EmailWizardStepStatus t2
    on t1.EmailJacketKey 
     = t2.EmailJacketKey
group
    by t1.EmailJacketKey
having count(*)
     = sum(case when t2.Completed = 1
                then 1 else 0 end )

note case inside sum() to count the 1 bits

bits don't sum

[Chris3125]

Thanks for the post, I am getting an "Incorrect syntax near the keyword 'group'." error.