December 5, 2011 at 9:47 am
When running two update commands (1 for each table I want to update in the view) on a view inside a transaction, does the view result set change before the second update command?
December 5, 2011 at 9:49 am
If it's ran precisely between the 2 updates then yes.
The answer changes depending on the isolation level of all the connections involved.
December 5, 2011 at 10:11 am
I suppose some code is in order.
This was the state of the code before the weekend.
View
ALTER view [dbo].[view_set_DispInvNoComp] AS
SELECT Dispatch.Dispatch, Dispatch.Invoice, Dispatch.RecDate, Dispatch.Complete, DispTech.Status, DispTech.Complete AS DispTechComplete, Sales.InvDate, DispTech.PromDate, DispTech.TPromDate, DispTech.TPromTime,DispTech.DispDate, DispTech.DispTime, DispTech.TimeOn,
DispTech.TimeOff, DispTech.DateOff, Employee.EmpName, Employee.EmpNo, DispTech.SortDate, DispTech.SortTime,
COUNT(case when dispatch.dispatch <> '' then 1 else null end) OVER(PARTITION BY dispatch.dispatch) AS entries,
COUNT(case when dispatch.dispatch <> '' AND DispTech.Complete <> 'Y' then 1 else null end) OVER(PARTITION BY dispatch.dispatch) AS NOTYs
FROM Dispatch INNER JOIN
DispTech ON Dispatch.Dispatch = DispTech.Dispatch INNER JOIN
Employee ON DispTech.ServiceMan = Employee.EmpNo INNER JOIN
Sales ON Dispatch.Invoice = Sales.Invoice
/*AND (Employee.EmpNo = '9003' OR
Employee.EmpNo = '9005' OR
Employee.EmpNo = '9006' OR
Employee.EmpNo = '9026' OR
Employee.EmpNo = '9014' OR
Employee.EmpNo = '9000' OR
Employee.EmpNo = '9015' OR
Employee.EmpNo = '9019' OR
Employee.EmpNo = '9991')
*/
Where(Dispatch.CustNo <> '0000011' AND Dispatch.CustNo <> '0005209') AND
Dispatch.Invoice <> '' AND
Dispatch.Complete IS NULL AND
Dispatch.RecDate < '2011-07-01'
/* AND (DispTech.DispDate IS NULL OR DispTech.DispDate < '2011-06-01') */
Transaction
/*select *
*/
USE Test2
DECLARE @intErrorCode INT
BEGIN TRANSACTION
/* UPDATE DISPATCH*/
UPDATE view_set_DispInvNoComp
SETComplete = InvDate
Where NOTYs = '1' AND DispTechComplete <> 'Y'entries = '1' AND
(EmpNo = '9003' OR
EmpNo = '9005' OR
EmpNo = '9006' OR
EmpNo = '9026' OR
EmpNo = '9014' OR
EmpNo = '9000' OR
EmpNo = '9015' OR
EmpNo = '9019' OR
EmpNo = '9991') AND Dispatch = '57693'
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
/* UPDATE DISPTECH*/
UPDATE view_set_DispInvNoComp
SET[Status] = 'Complete',
DispTechComplete = 'Y',
DispDate = InvDate,
DispTime = '12:00:00',
DateOff = InvDate,
TimeOff = '12:00:00',
SortDate = InvDate,
SortTime = '12:00:00'
Where entries = '1' AND
(EmpNo = '9003' OR
EmpNo = '9005' OR
EmpNo = '9006' OR
EmpNo = '9026' OR
EmpNo = '9014' OR
EmpNo = '9000' OR
EmpNo = '9015' OR
EmpNo = '9019' OR
EmpNo = '9991') AND Dispatch = '57693'
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRANSACTION
PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END
December 5, 2011 at 10:14 am
That changes nothing.
What are you trying to debug?
December 5, 2011 at 10:29 am
I added the view to the previous post.
I ran this as a test, and the first update made its changes and the second one did not, so I pose the question, since the first update modifies one of the filtering criteria of the view, which would in turn eliminate any updates from happening on the second update.
I may have just missunderstood the explinations of how one could use a transaction.
December 5, 2011 at 10:33 am
Yes the first update changes the data and there's nothing left to do in the 2nd one.
You need to do both updates in the same statement.
December 5, 2011 at 10:34 am
so...
I guess how do I do that, subquery the second update at the end of the first?
December 5, 2011 at 10:43 am
Something like that should do it.
UPDATE view_set_DispInvNoComp
SET Complete = CASE WHEN NOTYs = '1' AND DispTechComplete <> 'Y' THEN InvDate ELSE Complete END,
[Status] = 'Complete',
DispTechComplete = 'Y',
DispDate = InvDate,
DispTime = '12:00:00',
DateOff = InvDate,
TimeOff = '12:00:00',
SortDate = InvDate,
SortTime = '12:00:00'
Where entries = '1' AND
(EmpNo = '9003' OR
EmpNo = '9005' OR
EmpNo = '9006' OR
EmpNo = '9026' OR
EmpNo = '9014' OR
EmpNo = '9000' OR
EmpNo = '9015' OR
EmpNo = '9019' OR
EmpNo = '9991') AND Dispatch = '57693'
December 5, 2011 at 10:53 am
I do believe this was the first thing I tried, but was under the impression that even in the view I still could not update to (* typo here, I meant two *) tables in the one statement like that.
I will give it a try though.
December 5, 2011 at 10:57 am
amos-870870 (12/5/2011)
I do believe this was the first thing I tried, but was under the impression that even in the view I still could not update to tables in the one statement like that.I will give it a try though.
Why not?
I haven't done this since access back in 2000 so I'm a little rusty here.
December 5, 2011 at 11:04 am
Complete is part of a different table that the rest of columns.
View or function 'view_set_DispInvNoComp' is not updatable because the modification affects multiple base tables.
December 5, 2011 at 11:07 am
That to was suppose to be two in that post you quoted me on. I edited that post to reflect that.
Anyhow here are the results...
View or function 'view_set_DispInvNoComp' is not updatable because the modification affects multiple base tables.
December 5, 2011 at 11:09 am
amos-870870 (12/5/2011)
That to was suppose to be two in that post you quoted me on. I edited that post to reflect that.Anyhow here are the results...
View or function 'view_set_DispInvNoComp' is not updatable because the modification affects multiple base tables.
Can you reverse the updates?
If not the only idea I have is the do 1 update, output into #tmp
Then run the second updated based on the #tmp keys.
December 5, 2011 at 11:22 am
Ninja's_RGR'us (12/5/2011)
amos-870870 (12/5/2011)
I do believe this was the first thing I tried, but was under the impression that even in the view I still could not update to tables in the one statement like that.I will give it a try though.
Why not?
I haven't done this since access back in 2000 so I'm a little rusty here.
Couldn't do it in 2k either, but a lot of folks had trouble with this and it's rarely done, so don't feel too bad.
Short version: You cannot update multiple tables in a single update statement. If that update statement works against an updateable view you can only update one of the sub-tables at a time.
Ninja's correct, when you update one of the tables, you must expect that information to be updated (even within a transaction, since you're within your own transaction) when you do the second update. I definately would transactionalize this process.
The basic plan for this is usually update the primary table, noting any pertinent identification changes in the subtables in a local #tmp for reference, and then updating any subtables in the chain. It's a royal pain and it's why a lot of folks avoid updatable views.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 5, 2011 at 11:22 am
Reverse the updates?? Is there some tactick you refer to?
This was done to 1 test record in a test DB before I unleashed it on my live DB, if that is what you were refering to.
Yea that was what I was thinking originally, using temp tables, I thought there would be a more efficient way of accomplishing this. I guess I was wrong.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy