October 4, 2012 at 12:58 pm
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]
October 4, 2012 at 1:04 pm
whats the schema for closedJobSums?
October 4, 2012 at 1:06 pm
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
October 4, 2012 at 1:14 pm
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.October 4, 2012 at 1:18 pm
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]
October 4, 2012 at 1:49 pm
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]
October 5, 2012 at 1:56 am
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/
October 5, 2012 at 2:23 am
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.
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
October 5, 2012 at 2:43 am
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]
October 5, 2012 at 2:50 am
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.
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
October 5, 2012 at 7:53 am
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
October 5, 2012 at 9:05 am
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/
October 5, 2012 at 11:00 am
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]
October 8, 2012 at 6:22 am
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
October 8, 2012 at 9:00 am
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