UPDATE Does Nothing (Executing Query displays in status)

  • I am stumped and perplexed.

    The following statement executes correctly returns 2 Rows:

    select JobNum, RefreshDate, RecID from ClosedJobsSumms

    When this statement is executed nothing happens - the status shows Executing Query and the timer is clicking off the seconds.

    UPDATE cjs

    SET RefreshDate = '20120822 08:00'

    FROM ClosedJobsSumms cjs

    Other UPDATES have worked. For example, this executes OK, in a blink, so I know the UPDATE does execute on the table:

    UPDATE cjs

    SET QtyOrdered = QtyOrds.qtyord

    FROM ClosedJobsSumms cjs

    INNER JOIN (SELECT o.job_number,

    Sum(o.qty_ordered) AS QtyOrd

    FROM ERPDB.dbo.orders o

    LEFT JOIN ClosedJobsSumms cjs1

    ON cjs1.jobnum = o.job_number

    WHERE cjs1.jobnum IS NOT NULL

    AND cjs1.custid IS NULL

    GROUP BY o.job_number) QtyOrds

    ON QtyOrds.job_number = cjs.jobnum

    [font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

  • whats the schema for closedJobSums?

  • Unless there's something else blocking the update, it should run like that.

    I just ran this test:

    CREATE TABLE #T (

    JobNum INT,

    RefreshDate DATETIME,

    RecID INT);

    INSERT INTO #T (JobNum, RefreshDate, RecID)

    VALUES (1,GETDATE(), 1),(2,GETDATE(),2);

    UPDATE T

    SET RefreshDate = '20120822 08:00'

    FROM #T T;

    SELECT *

    FROM #T;

    Worked just fine.

    Try running this while your update is running:

    exec sp_who2;

    See if something is blocking the update.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • IF what you want to accomplish is to update every single row on CJS table then update should look like:

    UPDATE cjs

    SET RefreshDate = '20120822 08:00';

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Are you asking for: dbo

    i.e. dbo.ClosedJobsSumms

    All the other tables created in this DB are dbo.

    [font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

  • Problem Resolved: It was a PICNIC - Problem In Chair, Not In Computer

    I had run an UPDATE on the table earlier in another query window, but, had not COMMITted it.

    Discovered this problem when I decided I would close SSMS and start fresh. When closing the other query window I was notified that there were uncommitted transactions. Once COMMITted, the statement ran OK. Interesting the other statement in my post ran - 0 Rows Effected. It didn't occur to me until now it would 'execute' OK, since no records being updated meant the locked records would not have been involved.

    I did run the sp_who2; I'm not familiar with this sp, can intuitively understand the information in the columns. Did not see anything indicating uncommitted transactions. Not sure what phrase/term would indicate as such.

    Pablo - I wrote the statement that way because I am trying to get familiar with that structure. Repetition.

    Thank You All For Your Help. My apologies for my error.

    [font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

  • Hi,

    It should be

    UPDATE table_name

    SET RefreshDate = '20120822 08:00';

    rather than

    UPDATE cjs

    SET RefreshDate = '20120822 08:00';

    else it will throw error

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil190588 (10/5/2012)


    Hi,

    It should be

    UPDATE table_name

    SET RefreshDate = '20120822 08:00';

    rather than

    UPDATE cjs

    SET RefreshDate = '20120822 08:00';

    else it will throw error

    Almost, but not quite. The OP's statement is

    UPDATE cjs

    SET RefreshDate = '20120822 08:00'

    FROM ClosedJobsSumms cjs

    and it's absolutely fine. It's known as UPDATE FROM, and it's typically used to update a table with data from another and / or update a table filtered by a join with another.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • It's known as UPDATE FROM, and it's typically used to update a table with data from another and / or update a table filtered by a join with another.

    I realize that even though typical structure would be 'UPDATE {table-name} SET ...' when that is sufficient I am now using the structure below for 2 reasons. The 1st, as I had expressed, to get familiar with it when the situation is as you mentioned and now, from a tip I read yesterday morning, to easily check before/after performing an UPDATE:

    --SELECT

    -- {column-names...}

    UPDATE {table-name-alias}

    SET ...

    FROM {table-name}

    ...

    "Toggle" Commented out lines to quickly switch between viewing and updating:

    SELECT

    {column-names...}

    --UPDATE {table-name-alias}

    --SET ...

    FROM {table-name}

    ...

    [font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

  • EdA ROC (10/5/2012)


    It's known as UPDATE FROM, and it's typically used to update a table with data from another and / or update a table filtered by a join with another.

    I realize that even though typical structure would be 'UPDATE {table-name} SET ...' when that is sufficient I am now using the structure below for 2 reasons. The 1st, as I had expressed, to get familiar with it when the situation is as you mentioned and now, from a tip I read yesterday morning, to easily check before/after performing an UPDATE:

    --SELECT

    -- {column-names...}

    UPDATE {table-name-alias}

    SET ...

    FROM {table-name}

    ...

    "Toggle" Commented out lines to quickly switch between viewing and updating:

    SELECT

    {column-names...}

    --UPDATE {table-name-alias}

    --SET ...

    FROM {table-name}

    ...

    Absolutely spot on.

    There's no good reason - ever - for failing to check the SELECT version of an UPDATE FROM (or "joined update") and this makes it trivial to do.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • EdA ROC (10/5/2012)


    It's known as UPDATE FROM, and it's typically used to update a table with data from another and / or update a table filtered by a join with another.

    I realize that even though typical structure would be 'UPDATE {table-name} SET ...' when that is sufficient I am now using the structure below for 2 reasons. The 1st, as I had expressed, to get familiar with it when the situation is as you mentioned and now, from a tip I read yesterday morning, to easily check before/after performing an UPDATE:

    --SELECT

    -- {column-names...}

    UPDATE {table-name-alias}

    SET ...

    FROM {table-name}

    ...

    "Toggle" Commented out lines to quickly switch between viewing and updating:

    SELECT

    {column-names...}

    --UPDATE {table-name-alias}

    --SET ...

    FROM {table-name}

    ...

    I use Output and Rollback to do the same thing. Output tells me what rows will be affected, and from-to data.

    sp_who2 should have had a bit in the BlockBy column showing your open transaction blocking your other update. Won't see it now, but you could reproduce it by starting and leaving open another transaction, and trying this update again, in a proof-of-concept database. Easy enough to set up.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I write that statement as there was no FROM clause in his query.

    I agreed in using alias in FROM clause but he pasted query in whis he didnt use FROM 😉

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Thanks!

    sp_who2 should have had a bit in the BlockBy column showing your open transaction blocking your other update. Won't see it now, but you could reproduce it by starting and leaving open another transaction, and trying this update again, in a proof-of-concept database. Easy enough to set up.

    Phew! I re-created the scenario, then did a lot of combinations of having various "states" of UPDATES, Uncommitted and Committed, SELECTS, and the sp_who2 - to see/understand the consequences/results. Could see the Blkby. I have a better picture in my head. At the top is "when Executing Query is hung check for Uncommitted transactions, because I won't see the problem issuing sp_who2 in the query window that's hung.

    Appreciate the help! It's nice to end the week knowing you've learned something new.

    Have a fun weekend!

    [font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

  • EdA ROC (10/5/2012)


    Thanks!

    sp_who2 should have had a bit in the BlockBy column showing your open transaction blocking your other update. Won't see it now, but you could reproduce it by starting and leaving open another transaction, and trying this update again, in a proof-of-concept database. Easy enough to set up.

    Phew! I re-created the scenario, then did a lot of combinations of having various "states" of UPDATES, Uncommitted and Committed, SELECTS, and the sp_who2 - to see/understand the consequences/results. Could see the Blkby. I have a better picture in my head. At the top is "when Executing Query is hung check for Uncommitted transactions, because I won't see the problem issuing sp_who2 in the query window that's hung.

    Appreciate the help! It's nice to end the week knowing you've learned something new.

    Have a fun weekend!

    Ah! Yes, you have to run sp_who2 in it's own connection to find what's going on in this kind of scenario. Sorry for making assumptions about how you'd end up using it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Sorry for making assumptions about how you'd end up using it.

    Actually, I'm glad you didn't. Having to execute the different scenarios was good practice for me to understand it - sp_who2 and the different things that can happen. You could say it "drove the point home". Think of it this way, lessons learned: (1) If it does nothing suspect a block, (2) run sp_who2 in another connection and (3) I just learned sp_who2 is easier to review than looking at the Processes in the Activity Monitor. It's one thing to "know the textbook", it's more valuable to be able to use what you know.

    You're a natural teacher and don't even know it. (ha, ha). Thanks.

    [font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply