SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Update Question

  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Cleveland, TN
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update Question

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    --

    Quote Originally Posted by 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
    Last edited by StephenBauer; May 26, 2004 at 11:25.

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...
    Code:
    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
                  )
    Last edited by null; Apr 20, 2004 at 23:57.

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...or w/a join...hopefully someone can confirm this...
    Code:
    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

  6. #6
    SitePoint Member
    Join Date
    Apr 2004
    Location
    13th Floor
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    untested:
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Apr 2004
    Location
    13th Floor
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the post, I am getting an "Incorrect syntax near the keyword 'group'." error.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •