October 4, 2012 at 10:21 am
I'm testing an update query to correct an issue. The first time I ran the query below I thought it didn't update correctly (as I suspected). But subsequent runs are successful every time. Doesn't this query have to potential to update incorrectly by updating a row that has already been updated?
The real environment that requires corrections includes hundreds of user IDs, hundreds of tables and millions of rows. Is my smaple too small to make it fail?
set nocount on
if exists (select * from sysobjects where id = object_id('idtable') and OBJECTPROPERTY(id, 'IsUserTable') = 1) drop table idtable
if exists (select * from sysobjects where id = object_id('id_test') and OBJECTPROPERTY(id, 'IsUserTable') = 1) drop table id_test
Create table idtable (newid varchar(20), oldid varchar(20))
Insert into idtable
Values ('NT00000007','NT00000005') --Jane - new = 0007, old = 0005
Insert into idtable
Values ('NT0000000c','NT00000007') --Dick - new = 000c, old = 0007
-- create the transaction table and populate it
create table id_test(who varchar(20),ntuser char(10),trans_date int)
insert id_test(who, ntuser, trans_date)
select 'Entered By Dick', 'NT00000007', 2011 -- old record that needs to be updated
union all
select 'Entered By Jane', 'NT00000005', 2011 -- old record that needs to be updated
union all
select 'Entered By Dick', 'NT0000000c', 2012 -- new record that does not need to be updated
union all
select 'Entered By Jane', 'NT00000007', 2012 -- new record that does not need to be updated
select 'here''s what we start with'
select * from id_test
updatex
setx.ntuser = y.newid
fromid_test x join idtable y on (x.ntuser = y.oldid)
wherex.trans_date = 2011
select 'here''s what we end with'
select * from id_test
drop table idtable
drop table id_test
Thanks everyone.
October 4, 2012 at 11:30 am
The first time I ran the query below I thought it didn't update correctly (as I suspected). But subsequent runs are successful every time.
You think it didn't work or do you KNOW that it didn't work. An update query like this is not going to work one time and not work another time.
Doesn't this query have to potential to update incorrectly by updating a row that has already been updated?
Not sure what you mean here. There is only 1 update happening.
The real environment that requires corrections includes hundreds of user IDs, hundreds of tables and millions of rows. Is my smaple too small to make it fail?
I don't understand what failed? Why do you think something failed? Did you get an error message?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2012 at 12:18 pm
Sorry, when I said fail, I meant the final result set wasn't correct, not that it logged an error. A failure with the logic.
It's updating a person's old UserID to their new UserID. But one person's old (NT00000007 - Dick) is the same as one person's new = (NT00000007 - Jane). Couldn't it update an old UserID from NT00000005 to NT00000007 (example Jane) and then update that same row from NT00000007 to NT0000000c (example Dick)?
The repair script was delivered to me using a cursor walking the idtable, updating each person one at a time. That logic absolutely fails. So I removed the cursor and joined the idtable, thinking that logic was incorrect too. I ran it and thought I saw just what I described above. But I didn't take a screenshot. So I run it again and again and each time the result set is correct. And now I'm questioning myself. Did I only see what I expected to see? Does this query have flawed logic?
October 4, 2012 at 12:33 pm
Randy Doub (10/4/2012)
Sorry, when I said fail, I meant the final result set wasn't correct, not that it logged an error. A failure with the logic.It's updating a person's old UserID to their new UserID. But one person's old (NT00000007 - Dick) is the same as one person's new = (NT00000007 - Jane). Couldn't it update an old UserID from NT00000005 to NT00000007 (example Jane) and then update that same row from NT00000007 to NT0000000c (example Dick)?
The repair script was delivered to me using a cursor walking the idtable, updating each person one at a time. That logic absolutely fails. So I removed the cursor and joined the idtable, thinking that logic was incorrect too. I ran it and thought I saw just what I described above. But I didn't take a screenshot. So I run it again and again and each time the result set is correct. And now I'm questioning myself. Did I only see what I expected to see? Does this query have flawed logic?
Ahh now I get what you are saying. The cursor for this the wrong way to go about as you discovered for the logic problems (and we won't even go into performance). The way you are doing it should be fine. It won't update a row more than once like the cursor did.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2012 at 12:45 pm
Sean Lange (10/4/2012)
The first time I ran the query below I thought it didn't update correctly (as I suspected). But subsequent runs are successful every time.
You think it didn't work or do you KNOW that it didn't work. An update query like this is not going to work one time and not work another time.
Doesn't this query have to potential to update incorrectly by updating a row that has already been updated?
Not sure what you mean here. There is only 1 update happening.
The real environment that requires corrections includes hundreds of user IDs, hundreds of tables and millions of rows. Is my smaple too small to make it fail?
I don't understand what failed? Why do you think something failed? Did you get an error message?
The update does produce a different result the first time.
The first time you run the update, Dick (NT00000007) gets set to NT0000000c per your logic, and Jane (NT00000005) gets set to NT00000007.
The second time that you run the update, Dick no longer has a match in your second table. But Jane (now NT00000007) gets set to NT0000000c.
All subsequent times that you run the update, neither Dick nor Jane (NT0000000c) have a match in the second table, and so they remain as NT0000000c.
The flaw isn't so much in your code as in your data validation; if you don't want the above to occur, you cannot re-use id numbers.
October 4, 2012 at 1:01 pm
cphite (10/4/2012)
Sean Lange (10/4/2012)
The first time I ran the query below I thought it didn't update correctly (as I suspected). But subsequent runs are successful every time.
You think it didn't work or do you KNOW that it didn't work. An update query like this is not going to work one time and not work another time.
Doesn't this query have to potential to update incorrectly by updating a row that has already been updated?
Not sure what you mean here. There is only 1 update happening.
The real environment that requires corrections includes hundreds of user IDs, hundreds of tables and millions of rows. Is my smaple too small to make it fail?
I don't understand what failed? Why do you think something failed? Did you get an error message?
The update does produce a different result the first time.
The first time you run the update, Dick (NT00000007) gets set to NT0000000c per your logic, and Jane (NT00000005) gets set to NT00000007.
The second time that you run the update, Dick no longer has a match in your second table. But Jane (now NT00000007) gets set to NT0000000c.
All subsequent times that you run the update, neither Dick nor Jane (NT0000000c) have a match in the second table, and so they remain as NT0000000c.
The flaw isn't so much in your code as in your data validation; if you don't want the above to occur, you cannot re-use id numbers.
Holy cow I didn't even notice the ID was reused. As long as you only run the update once it should fine. The cursor approach would cause issues everytime. This way it will only be wrong the second time. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply